搜索并替换字符串 t-SQL

每个人我都试图编写一个查询来替换末尾出现的所有字符串。 我有一些干扰词（确切地说是 104 个），如果它们出现在字符串末尾，则需要将其从字符串中删除。

例如，两个干扰词是 --Company, LLC

以下是一些示例和预期输出：

American Company, LLC --Expected output --American (both noise words should be removed)
American LLC,LLC --Expected output -- American
American Company American Company-- American Company American (one noise word occurs in between other words, so it should not be removed)

目前我有这个查询：

DECLARE @NEWSTRING VARCHAR(max) 
DECLARE @NEWSTRINGlength nvarchar(max)

SET @NEWSTRING = 'American Company American Company Company, LLC  LLC' ; 

SET @NEWSTRINGlength = len(@newstring)
SELECT @NEWSTRINGlength

CREATE TABLE #item (item Nvarchar(250) null)

INSERT INTO #item

SELECT 'Company' as item
UNION ALL 
SELECT 'LLC' as item

DECLARE @unwantedCharecters  VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!, ]%'

WHILE PATINDEX( @unwantedCharecters, @NEWSTRING ) > 0
SELECT @NEWSTRING = ltrim(rtrim(Replace(REPLACE( @NEWSTRING, SUBSTRING( @NEWSTRING, PATINDEX( @unwantedCharecters, @NEWSTRING ), 1 ),''),'-',' ')))

SELECT @NEWSTRING = substring(rtrim(@NEWSTRING), 1, len(@newstring) - len(ITEM)) FROM #item WHERE  rtrim(@NEWSTRING) LIKE '%' + ITEM

每次出现的干扰词都应该被删除，除非它们出现在其他词之间。

这就能解决问题：

WITH 
DirtyValues AS(
    SELECT * FROM (VALUES
          (1, 'American Company, LLC') --Expected output --American (both noise words should be removed)
        , (2, 'American LLC,LLC') --Expected output -- American
        , (3, 'American Company American Company')-- American Company American (one noise word occurs in between other words, so it should not be removed)
    ) AS T(ID, Dirty)
),
NoisyWords AS(
    SELECT * FROM (VALUES
          (' ') -- Just apend the chars to be filtered to your noise word list
        , (',')
        , ('LLC')
        , ('Company')
    ) AS T(Noisy)
),
DoSomeMagic AS(
    SELECT ID
         , Result = REVERSE(Dirty)
    FROM DirtyValues 
    UNION ALL 
    SELECT ID
         , Result = SUBSTRING(Result, DATALENGTH(Noisy)+1, DATALENGTH(Result))
    FROM DoSomeMagic
        CROSS APPLY(
            SELECT 
                  Noisy = REVERSE(Noisy)
            FROM NoisyWords
        ) AS T
    WHERE PATINDEX('%' + Noisy + '%', Result) = 1
),
PickBestResult AS(
    SELECT DoSomeMagic.ID
         , [clean as a whistle] = REVERSE(DoSomeMagic.Result)
         , [Rank]               = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATALENGTH(Result) ASC)
    FROM DoSomeMagic
)
SELECT *
FROM PickBestResult
WHERE [Rank] = 1

它能做什么：

• 前 2 个 CTE 是您的数据集，您当然希望将它们更改为您自己的表。
• DoSomeMagic 是递归 CTE，首先反转字符串以便能够从末尾查找，然后交叉应用所有干扰词并检查字符串现在的开头是否以反向干扰词开头。如果是这样，请将其删除并继续，直到开始时没有发现干扰词。
• 然后，PickBestResult 将对每一行进行 [Rank]，结果最短的行将获得 Rank 1。