SQL - Replace characters using mapping, without a loop

Question

Is there a way to replace characters in SQL Server from a string using a mapping table and without using a loop.

I have mapping that can go like this:

a => b
b => c
...
z => a

This mapping is not static and can change. I tried the solution from https://stackoverflow.com/a/45202933/3161817 and https://stackoverflow.com/a/13051989/3161817 but I only end up having a string that are just a, like 'aaaaaaaa'

My current solution is like:

DECLARE @NextChar NCHAR(1)
DECLARE @Position int = 1
DECLARE @StrLength int = LEN(@str)
DECLARE @Result nvarchar(1000) = ''

WHILE (@Position <= @StrLength)
BEGIN
    SET @NextChar = SUBSTRING(@str, @Position, 1)

    SET @Result = @Result + ISNULL((SELECT ToChar FROM CharMapping
                                    WHERE @NextChar COLLATE Latin1_General_BIN = FromChar COLLATE Latin1_General_BIN
                                ), @NextChar)

    SET @Position= @Position + 1
END

but I'm looking for a possible solution without a loop.

I should have added that any character not in the mapping table should be left as is. abcdef-zz => bcdefg-aa Also, I need to use COLLATION so that uppercase/lowercase are replaced correctly using mapping. — pdube, Sep 13 '17 at 14:30

Tyron78 · Accepted Answer · 2017-09-14T06:11:20.720

DECLARE @t TABLE(
  src char
 ,dest char
)

INSERT INTO @t VALUES
 ('a', 'b')
,('b', 'c')
,('d', 'e')

DECLARE @TestString nvarchar(100) = 'aabbcdacbezzz';

WITH cte AS(
  SELECT 1 lvl, SUBSTRING(@TestString, 1, 1) AS TestPosChar, SUBSTRING(@TestString, 2, LEN(@TestString)-1) AS TestStringRemain
  UNION ALL
  SELECT lvl + 1, SUBSTRING(TestStringRemain, 1, 1), SUBSTRING(TestStringRemain, 2, LEN(TestStringRemain)-1)
    FROM cte
    WHERE LEN(TestStringRemain) >= 1
)
SELECT @TestString AS OldString
      ,SUBSTRING((SELECT ( '' + ISNULL(t.dest, TestPosChar))
                    FROM cte c
                    LEFT JOIN @t AS t ON t.src = c.TestPosChar
                    ORDER BY lvl
                    FOR XML PATH( '' )
                 ), 1, 1000 ) AS NewString

score 0 · Answer 2 · answered Sep 13 '17 at 14:10

I made this test :

declare @MyTab table(
letter char
)

declare @MyTab2 table(
letter char
)

insert into @MyTab
select substring(a.b, v.number+1, 1) 
from (select 'ABCDEFGHZZZ' b) a
join master..spt_values v on v.number < len(a.b)
where v.type = 'P'

insert into @MyTab2
select NewLetter
from (
select case letter when 'Z' then 'A'
    when 'z' then 'a'
    else char(ascii(letter)+1) end NewLetter
from @MyTab
) MyView

select stuff(
(select ''+letter from @MyTab2
for xml path('')),1,0,'')

etsa · Answer 3 · 2017-09-13T15:08:30.810

Try (and expand) following query, which use XML PATH, a tally number table and a decode table:

    CREATE TABLE #TTMMPP (ORIG CHAR(1), NEWC CHAR(1));
     /* add all values to shift */
      INSERT INTO #TTMMPP VALUES ('a','b'),('b','c'),('c','d'),('d','e'),('e','f')  /*, ....*/
  ;

    /* N as max len of your string */
     CREATE TABLE #TTMMPP2 (N smallint);
     DECLARE @I INT
     DECLARE @ROWS INT    
     SET @I = 1
     SET @ROWS = 1000
     WHILE @I < @ROWS
     BEGIN
     INSERT INTO #TTMMPP2 VALUES (@I)
     SET @I = @I + 1
     END

     ----------------------------------------    
     DECLARE @my_str VARCHAR(100) = 'abcd';

     SELECT  @my_str AS ORIGINAL, 
         (
            SELECT ''+C.NEWC
                    FROM (
                         SELECT N, SUBSTRING( @my_str, N,1) AS X, B.NEWC
                         FROM #TTMMPP2 A
                         INNER JOIN #TTMMPP B ON SUBSTRING(@my_str,A.N,1)= B.ORIG
                         WHERE N<=LEN(@my_str)
                         ) C
                         FOR XML PATH('')
                          ) AS SHIFTED;

Output:

ORIGINAL    SHIFTED
abcd    bcde

Updated version: if you want "mark" character not found in decode table you can use this (little changes to query: LEFT JOIN and COALESCE):

 DECLARE @my_str VARCHAR(100) = 'abcdefg'; 
SELECT  @my_str AS ORIGINAL, 
     (
        SELECT ''+C.NEWC
                FROM (
                     SELECT N, SUBSTRING( @my_str, N,1) AS X, COALESCE(B.NEWC,'*') AS NEWC
                     FROM #TTMMPP2 A
                     LEFT JOIN #TTMMPP B ON SUBSTRING(@my_str,A.N,1)= B.ORIG
                     WHERE N<=LEN(@my_str)                   
                     ) C
                     ORDER BY N
                     FOR XML PATH('')
                      ) AS SHIFTED;

Output (* substitute character not found in decode table):

ORIGINAL    SHIFTED
abcdefg bcde***

New update (as your last comment added):

SELECT  @my_str AS ORIGINAL, 
     (
        SELECT ''+C.NEWC
                FROM (
                     SELECT N, SUBSTRING( @my_str, N,1) AS X, COALESCE(B.NEWC,SUBSTRING(@my_str,A.N,1)) AS NEWC
                     FROM ##TTMMPP2 A
                     LEFT JOIN #TTMMPP B ON SUBSTRING(@my_str,A.N,1) COLLATE Latin1_General_BIN = B.ORIG COLLATE Latin1_General_BIN
                     WHERE N<=LEN(@my_str)                   
                     ) C
                     ORDER BY N
                     FOR XML PATH('')
                      ) AS SHIFTED

Output:

ORIGINAL    SHIFTED
abcdefgA    bcdeefgA

Dan Guzman · Answer 4 · 2017-09-13T14:40:35.663

0

SQL Server 2017 introduces a TRANSLATE function, which is similar to nested REPLACE functions. You didn't specify what version of SQL Server you are using so I don't know if that's an option.

SELECT TRANSLATE(@SourceString, 'abcdefghijklmnopqrstuvwxyz', 'bcdefghijklmnopqrstuvwxyza');

edited Sep 13 '17 at 14:40

answered Sep 13 '17 at 14:32

Dan Guzman

43,250
3
46
71

Using SQL 2012. – pdube Sep 13 '17 at 14:35
@Dan You mean TRANSLATE () .. you wrote REPLACE – etsa Sep 13 '17 at 14:36

SQL - Replace characters using mapping, without a loop

4 Answers4