问题描述
我正在使用 SQL Server,我想创建一个 SELECT 查询,用另一个表中使用的词替换字符串中的一个或多个词.像这样:
I'm using SQL Server and I want to create a SELECT query that replaces one or more words in a string with words used in another table. Like this:
SELECT [Message] from Table1
返回:
Hello, my name is Thomas and i'm from Belium.
在 Table2 我有两列
Original_Word-------Replace_Word is------------------------is not i'm-------------------------i am
所以我需要的选择查询必须返回:
So the select query I need must return this:
Hello, My name is not Thomas and i am from Belgium
有人可以帮忙吗?
推荐答案
可以使用动态sql构建嵌套替换:
You can use dynamic sql to build a nested replace:
DECLARE @sql varchar(max) = ''' '' + [Message] + '' '''; SELECT @sql = 'REPLACE(' + @sql + ',''' + REPLACE(' '+Original_Word+' ','''','''''') + ''',''' + REPLACE(' '+Replace_Word+' ','''','''''') + ''')' FROM Table2; SET @sql = 'SELECT LTRIM(RTRIM(' + @sql + ')) FROM Table1'; PRINT(@sql) EXECUTE (@sql);