我有两个表:
Word_Blacklist
| Black_Word | Replace_Word
1 | foo | f**
2 | bar | b**
和ESCodes
| Beircht
1 | this bar has good food
2 | foo foo sentences
从 ESCodes 中选择 Bericht 时,我想用 Replace_Word 替换所有出现的子字符串Black_Word,以便我的结果如下所示:
this b** has good f**d
f** f** sentences
我该怎么做?
编辑:您可以使用动态替换来执行此操作。有人建议将此作为对另一个问题的回答。
-- first generate the replace statement with all Black_Words
declare @replace varchar(max)
select @replace = coalesce ('replace(' + @replace,'replace(bericht') + ',''' + Black_Word + ''',''' + Replace_Word + ''')'
FROM Word_Blacklist
-- execute statement
exec( 'select ' + @replace + 'from ESCodes')
这应该适合您
select IIF(exists(select name from table2), '****', name)
from table1
如果您需要高性能解决方案,可以使用 Eval SQL.NET 轻松完成
免责声明:我是项目Eval SQL.NET 的所有者
该库允许您直接在 SQL 中使用 C# 语法。
DECLARE @tableMessage TABLE ( Msg VARCHAR(250) )
DECLARE @tableReplace TABLE
(
Original_Word VARCHAR(50) ,
Replace_Word VARCHAR(50)
)
INSERT INTO @tableMessage
( Msg )
VALUES ( 'A test to replace FromWord1 and FromWord2!' ),
( 'FromWord1FromWord1FromWord1 FromWord1 FromWord2' )
INSERT INTO @tableReplace
( Original_Word, Replace_Word )
VALUES ( 'FromWord1', 'ToWord1' ),
( 'FromWord2', 'ToWord2' )
DECLARE @sqlnet SQLNET = SQLNET::New('')
DECLARE @sql VARCHAR(MAX) = 'template'
DECLARE @pos VARCHAR(10) = '0';
-- CREATE the sql and set parameter value
SELECT @sql = @sql + '.Replace(old_' + @pos + ', new_' + @pos + ')' ,
@sqlnet = @sqlnet.ValueString('old_' + @pos, Original_Word)
.ValueString('new_' + @pos, Replace_Word) ,
@pos = CAST(@pos AS INT) + 1
FROM @tableReplace
-- SET the code to evaluate
-- template.Replace(old_0, new_0).Replace(old_1, new_1)
SET @sqlnet = @sqlnet.Code(@sql).Root();
-- Evaluate the code
SELECT Msg ,
@sqlnet.ValueString('template', Msg).EvalString()
FROM @tableMessage AS A