从表中查找和替换

Question

我有一些文本table_A，我需要从table_b中选择一个新文本并在table_a上替换。

这是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

这是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

到目前为止，我做了什么但没有成功：

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

不好的结果：

Lorem ipsum dolor sit amet， consectetur adipiscing elit， sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.测试 3a Test 3b Test 3cUt enim ad minim veniam， quis nostrud exercitation Ullamco laboris nisi ut aliquip ex ea commodo consequat.{T1}杜伊斯 aute irure dolor in reprehenderit in voluptate velit esse cillum 多洛尔 EU fugiat nulla pariatur.{T2}Excepteur sint occaecat Cupidatat non proident， sunt in culpa qui officia deserunt mollit anim id est laborum.测试4a 测试 4b 测试 4cLorem ipsum dolor sit Amet， consectetur adipiscing elit， sed do eiusmod tempor incididunt ut Labore et dolore magna aliqua.

预期结果：

Lorem ipsum dolor sit amet， consectetur adipiscing elit， sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.测试3a、测试 3b、测试 3c乌特 Enim ad minim veniam， quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.测试1a、测试 1b、测试 1cDuis aute irure dolor in Reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla 帕里亚图尔。测试2a、测试 2b、测试 2cExcepteur sint occaecat cupidatat non proident， sunt in Culpa qui officia deserunt mollit anim id est laborum.测试4a、测试 4b、测试 4c洛雷姆 Ipsum dolor sit amet， consectetur adipiscing elit， sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

欢迎任何建议/更正！

Answer 1

扩展我的评论。因为LAG()只考虑T2表中的当前行和前一行，所以它只能对两行执行查找和替换。您的order by和limit正在获取{t4}和(从滞后({t3}的替换，并删除结果(记录 2 和 3(，其中{t1}和/或{t2}替换在范围内。

相反，您可以在处理所有令牌时递归地通过T2表查找和替换。

考虑：

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示例