replace every character of a string with another character in ms server 2014

Question

I am using Microsoft SQL Server 2014 and I am trying to update some of the columns in my table.

I want to replace every character of a string with another character.

For example, the word:

HELLO123

I want to replace H with T, E with Q, L with Y, O with I, 1 with 6, 2 with 7 and 3 with 8 and so for.

I'm not sure if Microsoft SQL Server 2014 supports regular expression, i.e. creating a function and looping through every character and replace will take a long time on a table with a couple of millions of rows.

Does anyone has any solution that works like regular expression and can be fast?

Thanks

Answer 1

If you haven't noticed, the problem with REPLACE is that you'll need to nest the values, however, because you're nesting something like REPLACE(REPLACE('HELLO','H','E'),'E','Q') would return in 'QQLLO' not 'EQLLO'. As mentioned in the comments, SQL Server 2017 introduced TRANSLATE, which will only process a character once, however, as you're using 2014, you can';t use it (TRANSLATE('HELLO','HE','EQ')).

What you could do, is create a lookup table, and then split out the data into characters and rebuild it. This isn't going to be fast with a lot of data, and no, it's not going to get faster; but it'll "do the job":

--Create a table for the Cipher characters
CREATE TABLE dbo.CharCipher (InputChar char(1) NOT NULL,
                             OutputChar char(1) NOT NULL);
GO

--Add a Clustered Primary Key
ALTER TABLE dbo.CharCipher ADD CONSTRAINT PK_CharCipher PRIMARY KEY CLUSTERED (InputChar);
GO

--Ensure that the Output character us unique too    
CREATE UNIQUE NONCLUSTERED INDEX UX_CipherOutput ON dbo.CharCipher (OutputChar);
GO

--Add your Ciphers
INSERT INTO  dbo.CharCipher (InputChar,
                             OutputChar)
VALUES ('H','T'),
       ('E','Q'),
       ('L','Y'),
       ('O','I'),
       ('1','6'),
       ('2','7'),
       ('3','8');
GO

--Create a Sample table
CREATE TABLE dbo.YourTable (YourString varchar(15));
INSERT INTO dbo.YourTable (YourString)
VALUES('HELLO123');
GO

--And now the "Mess"... I mean solution
WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (8000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3, N N4)
SELECT YT.YourString,
       (SELECT ISNULL(CC.OutputChar,V.YourChar)
        FROM Tally T
             CROSS APPLY (VALUES(CONVERT(char(1),SUBSTRING(YT.YourString,T.I,1))))V(YourChar)
             LEFT JOIN dbo.CharCipher CC ON V.YourChar = CC.InputChar
        WHERE T.I <= LEN(YT.YourString)
        ORDER BY T.I
        FOR XML PATH(''),TYPE).value('.','varchar(8000)') AS NewString
FROM dbo.YourTable YT;

GO

--Clean up
DROP TABLE dbo.YourTable;
DROP TABLE dbo.CharCipher;