Find pattern and Replace 16 to 20 character after the pattern

Question

We have a table in oracle database, portion of data in CLOB column needs to be replaced.

the requirment is to replace the 16 to 20 char long account number, which always appear after ADAC key word

input string = 'ADAC 1345HFT5678MK 345WE' expected output= 'ADAC XXXXXXXXXXXXXXXXXXX'

is it doable using regexp_replace?

Answer 1

With a few caveats, this kind of replacement can generally be done with regexp_replace. I'll add an example below, that uses space as a delimiter to find the ADAC keyword and following account number. (If spaces are allowed in account-numbers, this would need to be adjusted)

Here's an example of this sort of replacement.

Create the test table:

CREATE TABLE ACCOUNT_REPLACEMENT(
  ACCOUNT_ID NUMBER,
  ACCOUNT_DATA CLOB
);

And some test data:

INSERT INTO ACCOUNT_REPLACEMENT VALUES (1,'#_# ADAC 1345HFT5678MK_345WE 359e,ne');
INSERT INTO ACCOUNT_REPLACEMENT VALUES (2,'143cN ADAC 1345HFT5678MK_345WE Wookie');
INSERT INTO ACCOUNT_REPLACEMENT VALUES (3,'ADAC 1234567890123456 ADACADAC ADAC abcdefghijklmnopqrst');
INSERT INTO ACCOUNT_REPLACEMENT VALUES (4,'ADAC abcdefghijklmnopqrstuvxyz');

Initial state:

SELECT * FROM ACCOUNT_REPLACEMENT ORDER BY 1 ASC;

ACCOUNT_ID  ACCOUNT_DATA                                              
1           #_# ADAC 1345HFT5678MK_345WE 359e,ne                      
2           143cN ADAC 1345HFT5678MK_345WE Wookie                     
3           ADAC 1234567890123456 ADACADAC ADAC abcdefghijklmnopqrst  
4           ADAC abcdefghijklmnopqrstuvxyz            

Then make the update.
This update will find the any ADAC key followed by a space and preceded by a space or line-start, and update the following 16-20 characters with twenty X characters. But if the account number includes more than 20 characters, it will take no action. This update limits valid account numbers to [0-9a-zA-Z_] but could be adjusted to allow other valid account.

UPDATE ACCOUNT_REPLACEMENT
SET ACCOUNT_DATA = REGEXP_REPLACE(ACCOUNT_DATA,
                                  '(^| )(ADAC )([0-9a-zA-Z_]{16,20})( |$)',
                                  '\1\2XXXXXXXXXXXXXXXXXXXX\4');

4 rows updated.

Then check the result:

SELECT * FROM ACCOUNT_REPLACEMENT
ORDER BY 1 ASC;

ACCOUNT_ID  ACCOUNT_DATA                                                  
1           #_# ADAC XXXXXXXXXXXXXXXXXXXX 359e,ne                         
2           143cN ADAC XXXXXXXXXXXXXXXXXXXX Wookie                        
3           ADAC XXXXXXXXXXXXXXXXXXXX ADACADAC ADAC XXXXXXXXXXXXXXXXXXXX  
4           ADAC abcdefghijklmnopqrstuvxyz           

Here the account numbers in account_id 1 and 2 are obscured. account_id 3 had two eligible matches to obscure, while account_id 4 had no eligible account numbers.