CODEWITHSUNDEEP
sqloracle11g
tonyf
tonyf

Reputation: 35567

How to mask a mobile phone number portion with 'X'

Using Oracle SQL, how can I replace certain numbers with an 'X'.

For example, if I have a random mobile number:

0400 100 200 or 0400100200

I would like to mask out the number to: 

0400 XXX XXX and 0400XXXXXX

I have tried using TRANSLATE but unsure if this is the best approach.

Upvotes: 0

Views: 9548

Answers (3)

user14966193
user14966193

Reputation: 1

Sample mobile number:

1234567890

My output:

12xxxxxx90

SELECT
    CONVERT(VARCHAR(20), SUBSTRING(CONVERT(VARCHAR(20),x.MobileNo),1,2)) +
     'xxxxx' +
     CONVERT(VARCHAR(20), SUBSTRING(CONVERT(VARCHAR(20),x.MobileNo),LEN(x.MobileNo) - 1, LEN(x.MobileNo)))
     AS MobileNo
FROM TABLENAME x

Upvotes: 0

GarethD
GarethD

Reputation: 69789

You can use REGEX_REPLACE, e.g.

SELECT REGEXP_REPLACE(SUSBTR(PhoneNumber, 5), '[0-9]', 'X')

Will replace all numbers after the 4th character with `X', so a full example would be:

SELECT  SUSBTR(PhoneNumber, 1, 4) 
            || REGEXP_REPLACE(SUSBTR(PhoneNumber, 5), '[0-9]', 'X') AS Masked
FROM    T;

Example on SQL Fiddle

As has been pointed out in a comment, you can also use TRANSLATE as follows:

SELECT TRANSLATE(SUBSTR(PhoneNumber, 5), '0123456789', 'XXXXXXXXXX') AS TRANSLATE;

I have very little practical experience with Oracle so can't even guess at which one would perform better, the commenter (Alex Poole) does however have a score of 2.3k in Oracle compared to my 53. So if he is suggesting TRANSLATE will run faster, I would not argue. I'd suggest trying both and picking the one that works fastest on your set of data.

Upvotes: 5

Evenure
Evenure

Reputation: 197

I'd prefer to give an answer:

String str = "12345678";
int len = str.length() - 3;
StringBuilder replace = new StringBuilder();
for(int i = 0; i < len; ++i)
    replace.append( "X" );
replace.append( len >= 0 ? str.substring( len ) : "" );

Upvotes: -2

Related Questions