How to replace a string value with single character

Question

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

For example:

• If there is a varchar column (Name) with the value Rio De janerio, I want to mask it as xxx xx xxxerio.

• If it is a numeric column (acc number) with value 123 453 6987,then I want it to be displayed as 000 000 6987.

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

Mask the data except last four characters/numbers

Answer 1

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.

Answer 2

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

Answer 3

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)

Answer 4

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)