0
votes

I have the table_A with some text and I need to select a new text from table_b and replace on table_a.

This is the table_a:

select 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. {T3} Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. {T1} Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. {T2} Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. {T4} Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.'::text as textvalue

This is the table_b:

select '{T1}' as find, 'Test 1a Test 1b Test 1c' as newvalue
union all
select '{T2}' as find, 'Test 2a Test 2b Test 2c' as newvalue
union all
select '{T3}' as find, 'Test 3a Test 3b Test 3c' as newvalue
union all
select '{T4}' as find, 'Test 4a Test 4b Test 4c' as newvalue
order by find

What I've done so far but without success:

select z.newtextvalue
from
(select t2.find, t2.newvalue, t1.textvalue, replace(lag(replace(t1.textvalue,t2.find,t2.newvalue)) over(order by t2.find),t2.find,t2.newvalue) as newtextvalue
from
(select 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. {T3} Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. {T1} Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. {T2} Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. {T4} Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.'::text as textvalue) as t1,
(select '{T1}' as find, 'Test 1a Test 1b Test 1c' as newvalue
union 
select '{T2}' as find, 'Test 2a Test 2b Test 2c' as newvalue
union 
select '{T3}' as find, 'Test 3a Test 3b Test 3c' as newvalue
union 
select '{T4}' as find, 'Test 4a Test 4b Test 4c' as newvalue
order by find) as t2) as z
order by z.find desc limit 1

The bad result:

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Test 3a Test 3b Test 3c Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. {T1} Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. {T2} Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Test 4a Test 4b Test 4c Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

And the expected result:

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Test 3a, Test 3b, Test 3c Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Test 1a, Test 1b, Test 1c Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Test 2a, Test 2b, Test 2c Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Test 4a, Test 4b, Test 4c Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

Any suggestions / corrections are welcome!

1
I am Groot. Can you come up with a shorter, more readable example? - Laurenz Albe
@LaurenzAlbe, I tried to summarize, but the website forces me to detail or I can't post. Anyway, I have the index and new_text in table B, and I want to search in table A the part of the text that matches the index in table B and replace it with the text in table B, was that clear? - Groot
Examples are good, but please with a shorter text. - Laurenz Albe
Your LAG() is only going to take into account the prior row. So for {T4} you will only see {T4} and from your LAG() {T3}. Remove your LIMIT 1. Instead you may need to use a recursive CTE here so you can iterate for each find/replace and update the string. - JNevill

1 Answers

1
votes

To expand on my comment. Because LAG() is only going to consider the current row and prior row in your T2 table it can only perform a find and replace on two rows. Your order by and limit are grabbing the replacements for {t4} and (from your lag) {t3} and dropping the results (records 2, and 3) where {t1} and/or {t2} replacements were in scope.

Instead you can recursively work your way through your T2 table finding and replacing as you work way through all of your tokens.

Consider:

WITH RECURSIVE fandr AS
(
    SELECT replace(t1.textvalue, t2.find, t2.newvalue) as textvalue, find as found, findorder
    FROM t1, t2
    WHERE t2.findOrder = 1
    UNION ALL
    SELECT replace(fandr.textvalue, t2.find, t2.newvalue) as textvalue, t2.find, t2.findorder
    FROM fandr, t2
    WHERE t2.findOrder = fandr.findorder + 1
)
, t2 AS
(
    select 1 as findorder, '{T1}' as find, 'Test 1a Test 1b Test 1c' as newvalue
    union 
    select 2 as findorder, '{T2}' as find, 'Test 2a Test 2b Test 2c' as newvalue
    union 
    select 3 as findorder, '{T3}' as find, 'Test 3a Test 3b Test 3c' as newvalue
    union 
    select 4 as findorder, '{T4}' as find, 'Test 4a Test 4b Test 4c' as newvalue
    order by find
) 
, t1 AS 
(
    select 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. {T3} Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. {T1} Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. {T2} Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. {T4} Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.'::text as textvalue
)

SELECT * 
FROM fandr
ORDER BY findorder DESC LIMIT 1


+---+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---+
| 1 | Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Test 3a Test 3b Test 3c Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Test 1a Test 1b Test 1c Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Test 2a Test 2b Test 2c Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Test 4a Test 4b Test 4c Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. | {T4} | 4 |
+---+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---+

rextester example