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

Question

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

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Mar 3, 2022 at 15:16

SQLpro · Accepted Answer · 2022-03-03 15:38:18Z

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)

edited Mar 3, 2022 at 15:38

answered Mar 3, 2022 at 14:15

SQLpro

5,3121 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Colleric1 Over a year ago

Hello SQLPro, thank you very much for your support. But.. I've encountered a problem: when I have another description like : dser - SMS afsendt (without numbers) It doesn't work.

SQLpro Over a year ago

Read my correction please

Yitzhak Khabinsky · Accepted Answer · 2022-03-03 16:06:31Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related