how to mask the phone number



IBM's flagship relational database management system

how to mask the phone number

Postby kumarinfy » Wed Mar 29, 2017 7:32 pm

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 :)
kumarinfy
 
Posts: 2
Joined: Wed Mar 29, 2017 6:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: how to mask the phone number

Postby xRDK » Thu Apr 06, 2017 7:41 pm

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.
xRDK
 
Posts: 3
Joined: Wed Apr 05, 2017 10:04 pm
Has thanked: 0 time
Been thanked: 0 time

Re: how to mask the phone number

Postby kumarinfy » Mon Apr 10, 2017 2:35 pm

Thank you frnd for your help
kumarinfy
 
Posts: 2
Joined: Wed Mar 29, 2017 6:51 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post