How to mask characters with X in a varchar2 field in Oracle SQL

Question

I have a list of varchar records (column) varchar2(30) with names. How to mask characters inside the name with the listed criteria.

e.g.

Tristram Vladimir Chan   <---Original name
1234567890123456789012   <---ruler (character count)
.....XXXXX.....XXXXX.....<--- masking criteria: copy every 5 characters, mask next 5 characters to X
TristXXX XladimXX XXan   <--- expected results

What makes this tricky is the special treatment for the space " " character...

Whats the quickest way to accomplish this? any libraries, procedures or shortcuts that can be used? Thanks a lot guys!!!!

Answer 1

To replace everything except for a blank, you would use regexp_replace. If the names have a maximum length (what I assume) the statement is easy to write. If not, then you would need a loop, which you would realize with either a stored function or some trick. Here is a statement for names of up to 30 letters:

select substr(full_name,1,5) || regexp_replace(substr(full_name,6,5), '[^ ]', 'X') ||
       substr(full_name,11,5) || regexp_replace(substr(full_name,16,5), '[^ ]', 'X') ||
       substr(full_name,21,5) || regexp_replace(substr(full_name,26,5), '[^ ]', 'X') as masked_name
from
(  
  select 'Tristram Vladimir Chan ' as full_name from dual
)