patternsqlMinor

Multiple string replacements

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

replacementsstringmultiple

Problem

I've got a replacement map table

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)

and a table of strings

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

in which I need to replace bits of string based on the replacement map table so that I get the below results:

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

My current solution is to nest REPLACE function but due to the number of replacements that I need to do, it's not an elegant way to it.

Solution

A SQLCLR function could be used to emulate the TRANSLATE Transact-SQL function new to SQL Server 2017.

Function definition

```
CREATE ASSEMBLY [Translate] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030010EF1B590000000000000000E00002210B010B00000A000000060000000000006E280000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000182800005300000000400000A802000000000000000000000000000000000000006000000C000000E02600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000007408000000200000000A000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000050280000000000004800000002000500E0200000000600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133004007B00000001000011026F0600000A2D10036F0600000A2D08046F0600000A2C02022A036F0700000A046F0700000A2E0B7201000070730800000A7A160A2B38160B2B2602066A6F0900000A03076A6F0900000A331002066A04076A6F0900000A6F0A00000A0717580B076A036F0700000A32D00617580A066A026F0700000A32BE022A1E02280B00000A2A0042534A4201000100000000000C00000076322E302E35303732370000000005006C00000084010000237E0000F0010000A401000023537472696E6773000000009403000070000000235553000404000010000000234755494400000014040000EC01000023426C6F620000000000000002000001471502000900000000FA25330016000001000000090000000200000002000000040000000B0000000800000001000000010000000200000000000A0001000000000006003D0036000A00650050000A0099007E000600D100BE001300E500000006001401F40006003401F4000A0052017E0006007D0136000000000001000000000001000100010010001800000005000100010050200000000096006E000A000100D72000000000861878001500050000000000000000000100AB0000000200B10000000300B600190078001500210078004B00310078005100390078001500410078001500110067019E0111007201A20149007800A60111008F01AB0111009801B00109007800150020002B00560024000B0019002E001B00C4012E002300CD012E001300BB0144000B00190064000B00190084000B001900B6010480000000000000000000000000000000006E00000002000000000000000000000001002D000000000002000000000000000000000001004400000000000000003C4D6F64756C653E005472616E736C6174652E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C4368617273005472616E736C617465002E63746F72004D6963726F736F66742E53716C5365727665722E5365727665720053716C466163657441747472696275746500496E7075740046696E64005265706C6163650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F4C656E67746800417267756D656E74457863657074696F6E006765745F4974656D007365745F4974656D00000000006D460069006E006400200061006E00640020005200650070006C00610063006500200070006100720061006D006500740065007200730020006D007500730074002000680061007600650020007400680065002000730061006D00650020006C0065006E006700740068002E0000006163F700B9DC9F40AD8A41025ADA52DD0008B77A5C561934E0890A0003120912091209120903200001310100030054020D497346697865644C656E6774680054020A49734E756C6C61626C65005408074D617853697A65A00F000005200101111504200101088146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650154557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000032000020320000A042001010E042001030A052002010A0304070208080801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000010EF1B5900000000020000001C010000FC260000FC080000525344535E55F4481D743041A9330094C192BCB402000000633A5C55736572735C7061756C775C4F6E6544726976655C446F63756D656E74735C56697375616C2053747564696F20323031355C50726F6A656374735C5472616E736C6174655C5472616E736C6174655C6F

Code Snippets

CREATE ASSEMBLY [Translate] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030010EF1B590000000000000000E00002210B010B00000A000000060000000000006E280000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000182800005300000000400000A802000000000000000000000000000000000000006000000C000000E02600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000007408000000200000000A000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000050280000000000004800000002000500E0200000000600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133004007B00000001000011026F0600000A2D10036F0600000A2D08046F0600000A2C02022A036F0700000A046F0700000A2E0B7201000070730800000A7A160A2B38160B2B2602066A6F0900000A03076A6F0900000A331002066A04076A6F0900000A6F0A00000A0717580B076A036F0700000A32D00617580A066A026F0700000A32BE022A1E02280B00000A2A0042534A4201000100000000000C00000076322E302E35303732370000000005006C00000084010000237E0000F0010000A401000023537472696E6773000000009403000070000000235553000404000010000000234755494400000014040000EC01000023426C6F620000000000000002000001471502000900000000FA25330016000001000000090000000200000002000000040000000B0000000800000001000000010000000200000000000A0001000000000006003D0036000A00650050000A0099007E000600D100BE001300E500000006001401F40006003401F4000A0052017E0006007D0136000000000001000000000001000100010010001800000005000100010050200000000096006E000A000100D72000000000861878001500050000000000000000000100AB0000000200B10000000300B600190078001500210078004B00310078005100390078001500410078001500110067019E0111007201A20149007800A60111008F01AB0111009801B00109007800150020002B00560024000B0019002E001B00C4012E002300CD012E001300BB0144000B00190064000B00190084000B001900B6010480000000000000000000000000000000006E00000002000000000000000000000001002D000000000002000000000000000000000001004400000000000000003C4D6F64756C653E005472616E736C6174652E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C4368617273005472616E736C617465002E63746F72004D6963726F736F66742E53716C5365727665722E5365727665720053716C466163657441747472696275746500496E7075740046696E64005265706C6163650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6

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

Context

StackExchange Database Administrators Q#173530, answer score: 3

Revisions (0)

No revisions yet.