We need to mask data after a certain word to next 60 length(fixed) character-CodePudding

I was facing issue during masking a record for the below input :

Input:

The Domain Value is 32456 is getting from Spain to Madrid for String value differently . and this is the data for all kind of variable.

Output:

The ************************************************************ring value differently . and this is the data for all kind of variable.

Basically in above input 'Domain Value' is fixed word, so we need to mask data that is start from Domain Value to next 60 length(fixed).

I am trying below query but it is not masking up to next 60 length

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, 'Domain Value([. # -]*\s{60} ) ', 1, 1, 0, NULL) AS start_pos1, 
    REGEXP_INSTR( col, 'Domain Value([. # -]*\s{60} ) ', 1, 1, 1, NULL) AS end_pos1,
  FROM Table 
)

CodePudding user response：

Looks like a matter of some substrings:

SQL> with test (col) as
  2    (select 'The Domain Value is 32456 is getting from Spain to Madrid for String varies differently . and this is the data for all kind of variable.' from dual)
  3  select substr(col, 1, instr(col, 'Domain Value') - 1) ||
  4         lpad('*', 60, '*') ||
  5         substr(col, instr(col, 'Domain Value')   60) result
  6  from test  ;

RESULT
--------------------------------------------------------------------------------
The ************************************************************ring varies diff
erently . and this is the data for all kind of variable.


SQL>

CodePudding user response：

If the text 'Domain Value' could occur more than once in the text, a simple regexp_replace would do it.

with demo (col) as
     ( select 'The Domain Value is 32456 is getting from Spain to Madrid for String varies differently . and this is the data for all kind of variable.' from dual
       union all
       select 'The Domain Value is a secret.' from dual 
     )
select regexp_replace(col, 'Domain Value.{1,48}', rpad('*',60,'*')) as redacted
from   demo;

DBFiddle