CODEWITHSUNDEEP
oracle-database
EX08
EX08

Reputation: 15

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

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 ?

Upvotes: 0

Views: 143

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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 :-)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521279

Try using REGEXP_REPLACE here:

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

Upvotes: 0

MT0
MT0

Reputation: 167981

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

db<>fiddle here

Upvotes: 1

Related Questions