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 UNION ALL

SELECT 'This is ssn12345 string dob 12-09-1998 string' FROM DUAL UNION ALL

SELECT 'This is dob 12-09-1998 string' FROM DUAL;

7 rows affected

SELECT value,

CASE

WHEN start_pos2 > 0

THEN SUBSTR( value, 1, start_pos1 - 1)

|| RPAD('*', end_pos1 - start_pos1 - 1, '*')

|| SUBSTR(value, end_pos1, start_pos2 - end_pos1)

|| RPAD('*', end_pos2 - start_pos2, '*')

|| SUBSTR(value, end_pos2)

WHEN start_pos1 > 0

THEN SUBSTR( value, 1, start_pos1 - 1)

|| RPAD('*', end_pos1 - start_pos1, '*')

|| SUBSTR(value, end_pos1)

ELSE value

END AS masked_value

FROM (

SELECT value,

REGEXP_INSTR( value, 'ssn([ #]*\d+)+|dob([. -]*\d+)+', 1, 1, 0, NULL) AS start_pos1,

REGEXP_INSTR( value, 'ssn([ #]*\d+)+|dob([. -]*\d+)+', 1, 1, 1, NULL) AS end_pos1,

REGEXP_INSTR( value, 'ssn([ #]*\d+)+|dob([. -]*\d+)+', 1, 2, 0, NULL) AS start_pos2,

REGEXP_INSTR( value, 'ssn([ #]*\d+)+|dob([. -]*\d+)+', 1, 2, 1, NULL) AS end_pos2

FROM table_name

);