Replace words in a sentence with words from anothe

问题:

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

Returns:

Hello, my name is Thomas and i'm from Belium.

In Table2 I have two columns

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

Can anyone help?

回答1:

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);

回答2:

Disclaimer: I'm the owner of the project Eval SQL.NET

Here is a solution by using C# code in T-SQL using Eval SQL.NET.

This solution is a little bit more complex then "EXECUTE" but at least you are safe from SQL Injection. You can even improve the solution by using Regex.Replace instead.

Documentation: Use regular expression in SQL

DECLARE @tableMessage TABLE ( Msg VARCHAR(250) )

DECLARE @tableReplace TABLE
    (
      Original_Word VARCHAR(50) ,
      Replace_Word VARCHAR(50)
    )

INSERT  INTO @tableMessage
        ( Msg )
VALUES  ( 'Hello, my name is Thomas and i''m from Belium.' ),
        ( 'Another Template with is or is and i''m or not!' )


INSERT  INTO @tableReplace
        ( Original_Word, Replace_Word )
VALUES  ( 'is', 'is not' ),
        ( 'i''m', 'i am' )


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