多個字元串替換

我有一個替換地圖表

CREATE TABLE #ReplacementMap (old NVARCHAR(10), new NVARCHAR(10))
INSERT INTO #ReplacementMap VALUES ('A',5)
INSERT INTO #ReplacementMap VALUES ('C',9)
INSERT INTO #ReplacementMap VALUES ('D',4)

和一個字元串表

CREATE TABLE #String1 (name NVARCHAR(50), string1 NVARCHAR(100))
INSERT INTO #String1 VALUES ('John','AB')
INSERT INTO #String1 VALUES ('Kyle','ABC')
INSERT INTO #String1 VALUES ('Steven','ABCD')

其中我需要根據替換映射表替換字元串位，以便得到以下結果：

John,5B
Kyle,5B9
Steven,5B94

我目前的解決方案是嵌套REPLACE函式，但由於我需要進行大量替換，這不是一種優雅的方式。

SQLCLR 函式可用於模擬SQL Server 2017 中新增的TRANSLATETransact-SQL 函式。

函式定義

CREATE ASSEMBLY [Translate] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030010EF1B590000000000000000E00002210B010B00000A000000060000000000006E280000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000182800005300000000400000A802000000000000000000000000000000000000006000000C000000E02600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000007408000000200000000A000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000050280000000000004800000002000500E0200000000600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133004007B00000001000011026F0600000A2D10036F0600000A2D08046F0600000A2C02022A036F0700000A046F0700000A2E0B7201000070730800000A7A160A2B38160B2B2602066A6F0900000A03076A6F0900000A331002066A04076A6F0900000A6F0A00000A0717580B076A036F0700000A32D00617580A066A026F0700000A32BE022A1E02280B00000A2A0042534A4201000100000000000C00000076322E302E35303732370000000005006C00000084010000237E0000F0010000A401000023537472696E6773000000009403000070000000235553000404000010000000234755494400000014040000EC01000023426C6F620000000000000002000001471502000900000000FA25330016000001000000090000000200000002000000040000000B0000000800000001000000010000000200000000000A0001000000000006003D0036000A00650050000A0099007E000600D100BE001300E500000006001401F40006003401F4000A0052017E0006007D0136000000000001000000000001000100010010001800000005000100010050200000000096006E000A000100D72000000000861878001500050000000000000000000100AB0000000200B10000000300B600190078001500210078004B00310078005100390078001500410078001500110067019E0111007201A20149007800A60111008F01AB0111009801B00109007800150020002B00560024000B0019002E001B00C4012E002300CD012E001300BB0144000B00190064000B00190084000B001900B6010480000000000000000000000000000000006E00000002000000000000000000000001002D000000000002000000000000000000000001004400000000000000003C4D6F64756C653E005472616E736C6174652E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C4368617273005472616E736C617465002E63746F72004D6963726F736F66742E53716C5365727665722E5365727665720053716C466163657441747472696275746500496E7075740046696E64005265706C6163650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F4C656E67746800417267756D656E74457863657074696F6E006765745F4974656D007365745F4974656D00000000006D460069006E006400200061006E00640020005200650070006C00610063006500200070006100720061006D006500740065007200730020006D007500730074002000680061007600650020007400680065002000730061006D00650020006C0065006E006700740068002E0000006163F700B9DC9F40AD8A41025ADA52DD0008B77A5C561934E0890A0003120912091209120903200001310100030054020D497346697865644C656E6774680054020A49734E756C6C61626C65005408074D617853697A65A00F000005200101111504200101088146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650154557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000032000020320000A042001010E042001030A052002010A0304070208080801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000010EF1B5900000000020000001C010000FC260000FC080000525344535E55F4481D743041A9330094C192BCB402000000633A5C55736572735C7061756C775C4F6E6544726976655C446F63756D656E74735C56697375616C2053747564696F20323031355C50726F6A656374735C5472616E736C6174655C5472616E736C6174655C6F626A5C52656C656173655C5472616E736C6174652E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004028000000000000000000005E2800000020000000000000000000000000000000000000000000005028000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D00650000005400720061006E0073006C006100740065002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005400720061006E0073006C006100740065002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000703800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[Translate]
(
   @Input nvarchar(4000), 
   @Find nvarchar(4000), 
   @Replace nvarchar(4000)
)
RETURNS nvarchar(4000)
AS EXTERNAL NAME 
   [Translate].[UserDefinedFunctions].[Translate];

用法

SELECT
   S.[name],
   S.string1,
   Result = dbo.Translate(S.string1, N'ACD', N'594')
FROM #String1 AS S;

╔════════╦═════════╦════════╗
║  name  ║ string1 ║ Result ║
╠════════╬═════════╬════════╣
║ John   ║ AB      ║ 5B     ║
║ Kyle   ║ ABC     ║ 5B9    ║
║ Steven ║ ABCD    ║ 5B94   ║
╚════════╩═════════╩════════╝

這個簡單的展示實現使用區分大小寫的比較。

原始碼

using Microsoft.SqlServer.Server;
using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
   [SqlFunction(
       DataAccess = DataAccessKind.None,
       IsDeterministic = true,
       IsPrecise = true,
       SystemDataAccess = SystemDataAccessKind.None
   )]
   [return: SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 4000)]
   public static SqlChars Translate
       (
           [SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 4000)]
           SqlChars Input,
           [SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 4000)]
           SqlChars Find,
           [SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 4000)]
           SqlChars Replace
       )
   {
       if (Input.IsNull || Find.IsNull || Replace.IsNull)
       {
           // Return unchanged input for any NULL parameters
           return Input;
       }

       if (Find.Length != Replace.Length)
       {
           throw new ArgumentException("Find and Replace parameters must have the same length.");
       }

       // For each character in the input string
       for (int i = 0; i < Input.Length; i++)
       {
           // For each character in the Find string
           for (int j = 0; j < Find.Length; j++)
           {
               // If the character matches...
               if (Input[i] == Find[j])
               {
                   // ...replace it
                   Input[i] = Replace[j];
               }
           }
       }
       return Input;
   }
}

這也可以使用遞歸 SQL 來完成，儘管我不能說這樣做是否是個好主意。我確實ID在您的替換地圖表中添加了一列。為了測試我生成了 456976 個四個字元串的程式碼：

CREATE TABLE #ReplacementMap (
ID INT NOT NULL IDENTITY (1, 1), 
old NVARCHAR(10),
new NVARCHAR(10),
PRIMARY KEY (ID)
);

INSERT INTO #ReplacementMap VALUES ('A',5);
INSERT INTO #ReplacementMap VALUES ('C',9);
INSERT INTO #ReplacementMap VALUES ('D',4);


CREATE TABLE #String1 (
ID INT NOT NULL IDENTITY (1, 1),
string1 NVARCHAR(100)
);

WITH ALL_LETTERS AS (
   SELECT distinct CHAR(number) LETTER
   FROM master..spt_values
   WHERE number >= 65 AND number <= 90
)
INSERT INTO #String1 WITH (TABLOCK)
SELECT a1.LETTER + a2.LETTER + a3.LETTER + a4.LETTER
FROM ALL_LETTERS a1
CROSS JOIN ALL_LETTERS a2
CROSS JOIN ALL_LETTERS a3
CROSS JOIN ALL_LETTERS a4;

這是進行翻譯的程式碼：

WITH rec_cte AS (
   SELECT 
   s.ID
   , REPLACE(s.string1, rm.old, rm.new) new_string1
   , 1 replace_id
   FROM #String1 s
   INNER JOIN #ReplacementMap rm ON rm.ID = 1

   UNION ALL

   SELECT 
   s.ID
   , REPLACE(s.new_string1, rm.old, rm.new) new_string1
   , replace_id + 1
   FROM rec_cte s
   INNER JOIN #ReplacementMap rm ON rm.ID = replace_id + 1
)
SELECT ID, new_string1
FROM rec_cte
WHERE replace_id = (SELECT COUNT(*) FROM #ReplacementMap);

假設您有 S 行 in#String1和 R 行 in #ReplacementMap。我們對錶中的每一行進行#ReplacementMap連接，過濾到下一行，然後REPLACE()使用該行。#ReplacementMap一旦在SXR 行的完整結果集中沒有更多行，就會返回。這被子查詢過濾到最終的翻譯。該程式碼將執行 SXRREPLACE()操作和 R + 1 連接到單行結果集，以及一些內部 tempdb 操作。

只要您的替換字元串少於 101 個，這應該無需任何修改即可工作。該程式碼的執行似乎與 Adán Bucio 發布的解決方案類似。在我的機器上，這個查詢在大約 10 秒內完成，他的解決方案在 20 秒內完成。但是，您不應該在此基礎上選擇您的解決方案。您應該使用您最熟悉的任何程式碼，只要它滿足您的響應時間要求。

請注意，SQL Server 2017 有一個內置函式可以讓這種操作變得微不足道：TRANSLATE。

引用自：https://dba.stackexchange.com/questions/173530