Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table_name ( value ) AS
SELECT 'This is ssn1 string' FROM DUAL UNION ALL
SELECT 'This is ssn12 string' FROM DUAL UNION ALL
SELECT 'This is ssn12345 string' FROM DUAL UNION ALL
SELECT 'This is ssn123456789 string' FROM DUAL UNION ALL
SELECT 'This is ssn#12345 string ssn 5678 9765' FROM DUAL;
5 rows affected

WITH data ( value, masked_value, end_pos ) AS (
  SELECT value,
         REGEXP_REPLACE(
           value,
           'ssn([ #]?\d+)+',
           RPAD(
             '*',
             LENGTH(
               REGEXP_SUBSTR(value, 'ssn([ #]?\d+)+', 1, 1)
             ),
             '*'
           ),
           1,
           1
         ),
         REGEXP_INSTR(
           value,
           'ssn([ #]?\d+)+',
           1,
           1,
           1
         )
  FROM   table_name
UNION ALL
  SELECT value,
         REGEXP_REPLACE(
           masked_value,
           'ssn([ #]?\d+)+',
           RPAD(
             '*',
             LENGTH(
               REGEXP_SUBSTR(masked_value, 'ssn([ #]?\d+)+', end_pos, 1)
             ),
             '*'
           ),
           end_pos,
           1
         ),
         REGEXP_INSTR(
           masked_value,
           'ssn([ #]?\d+)+',
           end_pos,
           1,
           1
         )
  FROM   data
  WHERE  end_pos > 0
)
SEARCH DEPTH FIRST BY value SET value_order
SELECT value, masked_value
FROM   data
WHERE  end_pos = 0;


VALUE
MASKED_VALUE


This is ssn#12345 string ssn 5678 9765
This is ********* string *************

This is ssn1 string
This is **** string

This is ssn12 string
This is ***** string

This is ssn12345 string
This is ******** string

This is ssn123456789 string
This is ************ string


select regexp_replace(
'This is ssn12345 string',
 'ssn\S+',
Rpad( '*', length(regexp_substr('This is ssn12345 string','ssn\S+') ), '*') 
) 
from dual 


REGEXP_REPLACE('THISISSSN12345STRING','SSN\S+',RPAD('*',LENGTH(REGEXP_SUBSTR('THISISSSN12345STRING','SSN\S+')),'*'))


This is ******** string

VALUE	MASKED_VALUE
This is ssn#12345 string ssn 5678 9765	This is ******* string ***********
This is ssn1 string	This is **** string
This is ssn12 string	This is ***** string
This is ssn12345 string	This is ******** string
This is ssn123456789 string	This is ************ string