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.

Answer

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.