CODEWITHSUNDEEP
sqlsql-server
Shaikh ashraf
Shaikh ashraf

Reputation: 1

Masking some part of string from the right with XXXX using SQL

I want to mask some part of string using SQL, for e.g if the string is 

'example test 156425648'

I just want to mask the first 5 characters of the number from the right to i.e

'example test XXXXX5648'

masking will always be from the right.

I wanted this mask to be done in the select query itself, someone please help me with the query for it.

Upvotes: 0

Views: 8130

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

In SQL Server, you use the stuff() function with patindex() and replicate():

select stuff(str, patindex('%[0-9]%', str), 5, replicate('X', 5))
from (values ('example test 156425648')) v(str);

If you know the numbers are at the end of the string, you can still use stuff():

select stuff(str, len(str) - 8, 5, replicate('X', 5))
from (values ('example test 156425648')) v(str);

From the way the question is phrased, I have no reason to think that the number is in a fixed position, though.

Upvotes: 3

forpas
forpas

Reputation: 164099

According to your reqirements that the number will always be 9 digits long and at the end of the string:

declare @value varchar(100) = 'example test 156425648';
select left(@value, len(@value) - 9) + 'XXXXX' + right(@value, 4)

will give:

example test XXXXX5648

See the demo.

Upvotes: 2

Related Questions