Page 1 of 1

how to mask the phone number

PostPosted: Wed Mar 29, 2017 7:32 pm
by kumarinfy
Hi ,
could you please help me to prepare a select query to mask the phone no
my requirement is to mask the phone no except last 4 digits
please note that phone nos are not fixed length
Table column(PHONE_NO) having values like below
94758365494
0438278475859574
8428746857733774443

expected output sud be like below
xxxxxxx5494
xxxxxxxxxxxx9574
xxxxxxxxxxxxxxx4443

thanks in advance :)

Re: how to mask the phone number

PostPosted: Thu Apr 06, 2017 7:41 pm
by xRDK
Hello,

This is my example table;

RKNAM1      RKNAM2      RKFONE    
RYAN        K           123141245
RYAN        J           8887548  
RYAN        ZEK         858745787
********  End of data  ********  

Where the columns are;
RKNAM1 = First name
RKNAM2 = Surname
RKFONE = Phone number

The below SQL adds a MASK column, based on the RKFONE column;

SELECT CUSTOMERS.*,
REPEAT('X', (LENGTH(TRIM(RKFONE))-4)) ||
SUBSTRING(TRIM(RKFONE),(LENGTH(trim(RKFONE))-3),4) RKMASK
FROM XRDK/CUSTOMERS

Result;
RKNAM1      RKNAM2      RKFONE      RKMASK  
RYAN        K           123141245   XXXXX1245
RYAN        J           8887548     XXX7548  
RYAN        ZEK         858745787   XXXXX5787
********  End of data  ********              


===

It doesn't exactly mask the field but it looks like it does;
Repeat is used to repeat the character 'X' for whatever the result of "field length -4" is.
|| is concat (join what's on the left to what's on the right)
SUBSTRING is looking for the last 4 digits of the field

Hope it helps,
Ryan.

Re: how to mask the phone number

PostPosted: Mon Apr 10, 2017 2:35 pm
by kumarinfy
Thank you frnd for your help