replace a substring in oracle which does not have fixed length-CodePudding

I have column value like:

1.   input -- "This is ssn12345 string" 
2.   input -- "This is ssn 123456 string"
3.   input -- "This is ssn#12345 string"
4.   input -- "This is ssn#12345 string ssn1234678"
5.   input -- "This is ssn#12345 string ssn-123456"
6.   input -- "This is ssn#12345 string ssn 5678 9765"

Here ssn12345 can have dynamic length(mean length is not fixed).

output will be:

1.   output -- "This is ******** string"
2.   output -- "This is ********** string" 
3.   output -- "This is ********* string"
4.   output -- "This is ********* string *********"
5.   output -- "This is ********* string **********"
6.   output -- "This is ********* string *************"

I am trying to replace string which is starting from ssn and end with some integer values likes we are starting with ssn1234 or ssn124567.

I am trying below query but it is replacing with single * value.

select regexp_replace('This is ssn12345 string', 'ssn[^0-9.] ', '*') from dual

This is * string

I have to replace the value from ssn to next charcter with that number of * between these.

Can someone please tell me how this can be achieved in Oracle SQL? Let me know if more details are needed on the same.

CodePudding user response：

Just use "not White space" :

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

CodePudding user response：

You can use:

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;

Which, for the sample data:

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;

Outputs:

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

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

db<>fiddle here