Data transformation - mask a part of sequence from a string in SQL Server-CodePudding

I need your help. I would like to: Mask all the letters with "x" before the sequence "-SMS send".

My dataset is:

Desc	Desired output
user_m503418 - SMS send	xxxx_x503418 - SMS send
cyberx_323 - SMS send	xxxxxx_323 - SMS send

Thanks :)

CodePudding user response：

As a begining :

SELECT TRANSLATE(LEFT([Desc], CHARINDEX('- SMS send', [Desc]) - 1) COLLATE Latin1_General_100_CI_AI, 'abcdefghijklmnopqrstuvwxyz', 'xxxxxxxxxxxxxxxxxxxxxxxxxx')   RIGHT([Desc], LEN([Desc]) - CHARINDEX('- SMS send', [Desc])   1)

CodePudding user response：

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [Desc] VARCHAR(255));
INSERT INTO @tbl ([Desc]) VALUES
('user_m503418 - SMS send'),
('cyberx_323 - SMS send'),
('dser - SMS afsendt');
-- DDL and sample data population, end

DECLARE @searchFor VARCHAR(50) = 'abcdefghijklmnopqrstuvwxyz'
    , @sms VARCHAR(20) = '- SMS';

SELECT *
    , Result = TRANSLATE(LEFT([Desc], pos-1) COLLATE Latin1_General_100_CI_AI
            , @searchFor, REPLICATE('x', LEN(@searchFor))) 
          SUBSTRING([Desc], pos,255)
FROM @tbl
    CROSS APPLY (SELECT  CHARINDEX(@sms, [Desc])) AS t(pos);

Output

 ---- ------------------------- ----- ------------------------- 
| ID |          Desc           | pos |         Result          |
 ---- ------------------------- ----- ------------------------- 
|  1 | user_m503418 - SMS send |  14 | xxxx_x503418 - SMS send |
|  2 | cyberx_323 - SMS send   |  12 | xxxxxx_323 - SMS send   |
|  3 | dser - SMS afsendt      |   6 | xxxx - SMS afsendt      |
 ---- ------------------------- ----- -------------------------