Masking data after a particular word up to next word in oracle-CodePudding

I was facing issue during masking a data that is describe below --

	Input	Output
1.	CC# S2345-567 Match 2345 This is String	************** Match 2345 This is String
2.	My Self CC P457-238 This is Data	My Self *********** This is Data
3.	My Card is CC 457829	My Card is *********
4.	This is the CC# 42357 This is the my records	This is the ********* This is the my records .

I was able to mask the 3rd and 4rd condition by using below query --

SELECT CASE 
       WHEN start_pos1 > 0 
       THEN SUBSTR( col, 1, start_pos1 - 1) 
            || RPAD('*', end_pos1 - start_pos1, '*') 
            || SUBSTR(col, end_pos1) 
       ELSE col 
       END AS col_new 
FROM   ( 
  SELECT col, 
         REGEXP_INSTR( col, 'CC([. # -]*\d ) ', 1, 1, 0, NULL) AS start_pos1, 
         REGEXP_INSTR( col, 'CC([. # -]*\d ) ', 1, 1, 1, NULL) AS end_pos1
 FROM  Table 
);

Can anyone please help for above the 1st and 2nd condition ?

CodePudding user response：

You need to match the alphabetic character before the digits:

SELECT col,
       CASE 
       WHEN start_pos1 > 0 
       THEN SUBSTR( col, 1, start_pos1 - 1) 
            || RPAD('*', end_pos1 - start_pos1, '*') 
            || SUBSTR(col, end_pos1) 
       ELSE col 
       END AS col_new 
FROM   ( 
  SELECT col, 
         REGEXP_INSTR( col, 'CC#?\s [A-Z]?\d ([. -]*\d )*', 1, 1, 0, NULL) AS start_pos1, 
         REGEXP_INSTR( col, 'CC#?\s [A-Z]?\d ([. -]*\d )*', 1, 1, 1, NULL) AS end_pos1
  FROM  table_name
);

Which, for your sample data:

CREATE TABLE table_name (col) AS
SELECT 'CC# S2345-567 Match 2345 This is String' FROM DUAL UNION ALL
SELECT 'My Self CC P457-238 This is Data' FROM DUAL UNION ALL
SELECT 'My Card is CC 457829' FROM DUAL UNION ALL
SELECT 'This is the CC# 42357' FROM DUAL UNION ALL
SELECT 'The ABCC should not be masked' FROM DUAL;

Outputs:

COL COL_NEW

CC# S2345-567 Match 2345 This is String ************* Match 2345 This is String

My Self CC P457-238 This is Data My Self *********** This is Data

My Card is CC 457829 My Card is *********

This is the CC# 42357 This is the *********

The ABCC should not be masked The ABCC should not be masked

COL	COL_NEW
CC# S2345-567 Match 2345 This is String	************* Match 2345 This is String
My Self CC P457-238 This is Data	My Self *********** This is Data
My Card is CC 457829	My Card is *********
This is the CC# 42357	This is the *********
The ABCC should not be masked	The ABCC should not be masked

db<>fiddle here

CodePudding user response：

Try using REGEXP_REPLACE here:

SELECT col,
       REGEXP_REPLACE(col, 'CC#?[[:space:]] \S ', '********') AS col_masked
FROM yourTable;

CodePudding user response：

One more alternative:

WITH cteData
  AS (SELECT col, REGEXP_SUBSTR(col, 'CC#? [A-Z]?[-0-9] ') AS MATCH_DATA
        FROM table_name)
SELECT col, MATCH_DATA, REPLACE(col, MATCH_DATA, RPAD('*', LENGTH(MATCH_DATA), '*'))
  FROM cteData

db<>fiddle here

(And many thanks to @MT0 for the sample data :-)