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
WITH data ( value, masked_value, end_pos ) AS (
SELECT value,
REGEXP_REPLACE(
value,
'ssn([ #]?\d+)+|dob ?(-?\d+)+',
RPAD(
'*',
LENGTH(
REGEXP_SUBSTR(value, 'ssn([ #]?\d+)+|dob ?(-?\d+)+', 1, 1)
),
'*'
),
1,
1
),
REGEXP_INSTR(
value,
'ssn([ #]?\d+)+|dob ?(-?\d+)+',
1,
1,
1
)
FROM table_name
UNION ALL
SELECT value,
REGEXP_REPLACE(
masked_value,
'ssn([ #]?\d+)+|dob ?(-?\d+)+',
RPAD(
'*',
LENGTH(
REGEXP_SUBSTR(masked_value, 'ssn([ #]?\d+)+|dob ?(-?\d+)+', end_pos, 1)
),
'*'
),
end_pos,
| VALUE | MASKED_VALUE |
|---|---|
| This is dob 12-09-1998 string | This is ************** string |
| 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 ssn12345 string dob 12-09-1998 string | This is ******** string ************** string |
| This is ssn123456789 string | This is ************ string |