SQL Server 基于另一个表替换字符串

Question

我有一个字符串，我想通过引用查找表来替换其中的一些单词

create table LookupTab 
(
     oldvalue varchar(100),
     newvalue varchar(100)
);

insert into LookupTab 
values ('Run', 'Run Go'), ('Hide', 'Hide Mask'), ('Go', 'Go Run'), ('Mask', 'Mask Hide')

预期输出

string ='i have to go'     
result ='i have to Go Run'   <-- it should not again replace the word Run

string ='i have to go and go again'     
result ='i have to Go Run and Go Run again'

我试过的

CREATE FUNCTION [dbo].[TranslateString]
    (@Str nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @Result nvarchar(max) = @Str;

    SELECT @Result = REPLACE(@Result, Oldvalue, NewValue) 
    FROM LookupTab; 

    RETURN @Result;
END

但它再次替换了被替换的单词

Answer 1

Demo on dbfiddle

2个步骤:

找到所有要替换的单词。

替换为每个 @OldValue至 @NewValue相应的话。

CREATE FUNCTION [dbo].[TranslateString]
(
 @Str nvarchar(max)
)RETURNS nvarchar(max)
AS
BEGIN
  DECLARE @OldValue nvarchar(100);
  DECLARE @NewValue nvarchar(100);
  DECLARE @CHARINDEX INT = 0;
  DECLARE @Result nvarchar(100) = @Str;
  DECLARE @TempTable AS TABLE(OldValue varchar(100), NewValue varchar(100), isApply BIT) 

  --1. Region: Find all the words to replace
   WHILE (@CHARINDEX < LEN(@Str))
   BEGIN
     SELECT TOP 1 @OldValue = OldValue, @NewValue = newvalue, @CHARINDEX = CHARINDEX(oldvalue, @Str) 
      FROM LookupTab
        WHERE CHARINDEX(oldvalue, @Str) > @CHARINDEX
        ORDER BY CHARINDEX(oldvalue, @Str)

     IF(ISNULL(@OldValue, '') != '' AND NOT EXISTS(SELECT TOP 1 1 FROM @TempTable WHERE OldValue = @OldValue))   
         INSERT INTO @TempTable(OldValue, NewValue)
         VALUES(@OldValue, @NewValue)

     SET @CHARINDEX = @CHARINDEX + LEN(@OldValue);
   END
 --1. End-Region: Find all the words to replace

  --2. Region: Replace with each @OldValue to @NewValue word accordingly
  WHILE(EXISTS(SELECT OldValue FROM @TempTable WHERE ISNULL(isApply, 0) = 0))
  BEGIN
       SELECT @OldValue = OldValue, @NewValue = NewValue FROM @TempTable WHERE ISNULL(isApply, 0) = 0

       SET @Result = replace(@Result,@Oldvalue,@NewValue);
       UPDATE @TempTable SET isApply = 1 WHERE OldValue = @OldValue
  END
  --2. End-Region: Replace with each @OldValue to @NewValue word accordingly

  RETURN @Result;
END

输出


2020-01-20 更新

修复一些异常情况的新解决方案。 Demo in db<>fiddle

创建一个 strSplit能够将每个单词拆分成一个表格的函数

将每个单词替换为 ISNULL(l.newvalue, s.val)

替换成@Result后加入所有单词然后返回。

    CREATE FUNCTION [dbo].[TranslateString]
    (
     @Str nvarchar(max)
    )RETURNS nvarchar(max)
    AS
    BEGIN
        DECLARE @Result NVARCHAR(MAX)
        ;WITH cte_TempTable AS(
           select ISNULL(l.newvalue, s.val) AS Value 
           from strSplit(@Str, ' ') s
           left join LookupTab l on s.val = l.oldvalue
        )
        SELECT @Result = (SELECT Value + ' ' FROM cte_TempTable FOR XML PATH(''))

      RETURN @Result;
    END

输出