MSSQL - Masking data based on mapping table

Question

wanted to perform data masking according to mapping as below by using MSSQL 2008R2:

---

Mapping Table

A = C
B = A
C = E
1 = 3
2 = 1
3 = 9

Original

ABC123

Masked

CAE319

---

The idea would be using replace however the second replace function will replacing previous replaced value.

select Replace(Replace(Replace(Replace(Replace(REPLACE('ABC123', 'A', 'C'), 'B', 'A'), 'C', 'E'), '1', '3'), '2', '1'), '3', '9')

Result: CAE319

P.s. value edited, because Reverse or reverse replace cannot be use in this case

any idea?

Answer 1

If you want a more table approach.

There are two code segments below which will Mask or UnMask a string. Easily converted into a UDF or even placed in a CROSS APPLY

Declare @Mask table (MapFrom varchar(10),MapTo varchar(10))
Insert into @Mask values 
('A','C'),
('B','D'),
('C','E'),
('1','2'),
('2','3'),
('3','9')


Declare @Yourtable table (ID int,SomeCol varchar(max))
Insert Into @Yourtable values
(1,'ABC123')

-- To Mask
Declare @U varchar(max) ='ABC123'

Select NewSting = Stuff((Select ''+S 
    From (
        Select N
                ,S=IsNull(MapTo,Substring(@U,N,1))
            From (Select Top (Len(@U)) N=Row_Number() Over (Order By (Select null)) From master..spt_values)  N
            Left Join @Mask on Substring(@U,N,1)=MapFrom
        ) X 
    Order By N
    For XML Path ('')),1,0,'') 

-- To UnMask
Declare @M varchar(max) = 'CDE239'

Select NewSting = Stuff((Select ''+S 
    From (
        Select N
                ,S=IsNull(MapFrom,Substring(@M,N,1))
            From (Select Top (Len(@M)) N=Row_Number() Over (Order By (Select null)) From master..spt_values)  N
            Left Join @Mask on Substring(@M,N,1)=MapTo
        ) X 
    Order By N
    For XML Path ('')),1,0,'')