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

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

sqlsql-servertsql


Solution 1:[1]

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)

Solution 2:[2]

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      |
+----+-------------------------+-----+-------------------------+

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Yitzhak Khabinsky

Related Questions