CODEWITHSUNDEEP
sqlregexreplacepatindexsql-masking
MRR
MRR

Reputation: 83

How to replace a string value with single character

I want to mask the data except the last four characters.

For example:

I tried using right and replace function. But I could not find the solution.

Mask the data except last four characters/numbers

enter image description here

Upvotes: 2

Views: 1012

Answers (4)

Ed Bangga
Ed Bangga

Reputation: 13006

Using SQLSERVER STUFF() and PATINDEX() will solve this question

FOR MASKING THE NAME

declare @str varchar(20)
declare @rcInt int
set @str ='Rio De janerio'
set @rcInt = 4;

with cte as
(select left(@str, len(@str)-@rcInt) as name
), 
rn as
(select stuff(name, patindex('%[a-z]%',name), 1,  'X') as ch, patindex('%[a-z]%',name)as rn, name from cte
union all
select stuff(ch, rn+patindex('%[a-z]%',substring(name, rn+1, len(name))), 1, 'X') as ch, rn+patindex('%[a-z]%',substring(name, rn+1, len(name)))as rn, name from rn
where patindex('%[a-z]%',substring(name, rn+1, len(name)))>0
)
select top 1 ch + right(@str, @rcInt) from rn order by rn desc

FOR TELEPHONE NUMBER (as function)

create function ufn_MaskPhoneNum(
 @str varchar(20),
 @rcInt int
)
returns @tblReturn table(maskedStr varchar(100))
as
begin
    with cte as
    (select left(@str, len(@str)-@rcInt) as name
    ), 
    rn as
    (select stuff(name, patindex('%[0-9]%',name), 1,  'X') as ch, patindex('%[0-9]%',name)as rn, name from cte
    union all
    select stuff(ch, rn+patindex('%[0-9]%',substring(name, rn+1, len(name))), 1, 'X') as ch, 
        rn+patindex('%[0-9]%',substring(name, rn+1, len(name)))as rn, name from rn
    where patindex('%[0-9]%',substring(name, rn+1, len(name)))>0
    )
    insert into @tblReturn 
    select top 1  ch + right(@str, @rcInt) from rn order by rn desc
    return;
end

sample: 

select * from ufn_MaskPhoneNum('123 453 6987', 4)

Upvotes: 0

Red Devil
Red Devil

Reputation: 2393

If you are using SQL Server 2016 and above you can use Translate function:

declare @name varchar(50) = 'Rio De janerio'

select translate(substring(@name,1,len(@name)-4),'abcdefghijklmnopqrstuvwxyz','**************************') + right(@name,4)

declare @i int = 1234536987
select translate(substring(convert(varchar(50),@i),1,len(@i)-4),'0123456789','0000000000') + right(@i,4)

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13519

On Oracle, You can try below query to replace numbers with 0s - 

SELECT REGEXP_REPLACE (SUBSTR('123 453 6987', 1, LENGTH('123 453 6987')-4), '\d', '0') || SUBSTR('123 453 6987', -4, 4) PROCESSED_STRING
FROM DUAL;

PROCESSED_STRING
000 000 6987

For Replacing strings with #s - 

SELECT REGEXP_REPLACE (SUBSTR('Rio De janerio', 1, LENGTH('Rio De janerio')-4), '\S', '#') || SUBSTR('Rio De janerio', -4, 4) PROCESSED_STRING
FROM DUAL;

PROCESSED_STRING
### ## ###erio

Here is the fiddle.

Upvotes: 1

saravanatn
saravanatn

Reputation: 630

This is not a complete solution but it is just a idea and a head up to move you in the right direction . You can modify the query as per your requirement .

In SQL SERVER :

create table place
(
place_name varchar(100),
place_pincode varchar(100)
)


insert into place values ('Rio De janerio',1234536987)

select substring(place_pincode,len(place_pincode)-3,len(place_pincode)),
case when isnumeric(place_pincode)=1 then 
concat('000000',substring(place_pincode,len(place_pincode)-3,len(place_pincode)))  end as place_pincode,
case when isnumeric(place_name)<>1 then 
concat('XXXXXX',substring(place_name,len(place_name)-3,len(place_name)))  end as place_name
from place

Upvotes: 0

Related Questions