TSQL:在一个查询中多次替换相同的字段 [英] TSQL: Replace same field multiple times in one query

问题描述

给定一个替换表，是否可以将所有替换应用于同一个查询中的表中的列?

Given a table of replacements, can all the replacements be applied to a column in a table in the same query?

免责声明:我可以使用游标或动态 sql 来创建嵌套的替换字符串；我想知道是否可以简明扼要地完成.

Disclaimer: I can do this with a cursor, or with dynamic sql to create a nested string of replacements; I want to know if it could be done concisely.

我有一个替换表，

create table #replacements(old varchar(max), new varchar(max))
insert into #replacements values
('X','Y'),
('A','B'),
('W','V'),
('C','D')

和要替换的值表:

create table #value(value varchar(max))
insert into #value values
('XA'),
('WC')

我想在一个返回的查询中执行这些:

I'd like to perform these in one query which gives back:

YB

VD

有没有办法做到这一点?我试过了，

Is there any way to do this? I tried,

update v
set value = replace(value,old,new)
from #value v,
#replacements

但这给出了(只完成了完整连接中的第一行):

but this gives (only the first row in the full join is done):

是的

厕所

推荐答案

JBond 的回答很简单，但是对于任何大量的行来说它都很慢，因为它将是 RBAR.他的函数必须一个一个地获取每个值，然后在你的替换表中运行它.对于大量行，您会看到一些严重的性能问题.

JBond's answer is simple but it's SLOW for any large number of rows since it will be RBAR. His function has to grab each value one by one and then run it through your replacement table. With a large number of rows, you'll see some serious performance issues.

这是一个动态查询，在我看来，它比 Vasily 的简单一点，尽管两者实际上做的是相同的事情.代码对于任意数量的行都应该表现得非常好.试试看:

Here's a dynamic query that is, in my opinion, a little simpler than Vasily's, although both really do the same thing. The code should perform really well for any number of rows. Try it out:

DECLARE @Replace VARCHAR(MAX);

SELECT @Replace = COALESCE('REPLACE(' + @Replace,'REPLACE(value') + ',''' + old + ''',''' + new + ''')'
FROM #replacements


EXEC
(
    'UPDATE #value
    SET Value = ' + @Replace
)

递归解决方案

WITH CTE_replacements
AS
(
    SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) row_num,
            old,
            new
    FROM #replacements
),
CTE_recursion
AS
(
    SELECT  REPLACE(value,old,new) AS value,
            1 AS cnt
    FROM #value
    CROSS APPLY (SELECT old,new FROM CTE_replacements WHERE row_num = 1) CA

    UNION ALL

    SELECT  REPLACE(value,old,new) AS value,
            cnt + 1
    FROM cte_recursion A
    CROSS APPLY (SELECT old,new FROM CTE_replacements WHERE row_num = cnt + 1) CA

)

SELECT TOP(SELECT COUNT(*) FROM #value) *
FROM CTE_recursion
ORDER BY 2 DESC
OPTION (MAXRECURSION 0)

与动态SQL方案相比，这不是很好.相比于功能，它更好.递归的作用是将每个更改一一应用于整个数据集.所以替换行应用于所有数据.然后将第二个更改(行)应用于整个数据集等...因此对于少量更改，因为它通过了 RBAR，然后设置了一个不太大的值，它将起作用正好.

Compared to the dynamic SQL solution, this is not great. Compared to the function, it is better. What the recursion does is apply each change one by one to the entire dataset. So the row in replacement is applied to all the data. Then the second change(row) is applied to the entire dataset etc... So for a small number of changes because it goes through it RBAR, and then a not too large value set, it will work just fine.

这篇关于TSQL:在一个查询中多次替换相同的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！