We need to mask data for the String up to fixed length in Oracle-CodePudding

I am trying to mask the data for the below String :

This is the new ADHAR NUMBER 123456789989 this is the string 3456798983 from Customer Name like 345678 to a String .

In above data I want to mask data starting from ADHAR NUMBER to length up to 60 characters.

OUTPUT :

This is the new *********************************************************Customer Name like 345678 to a String .

Can anyone please help

CodePudding user response：

A little bit of substr instr does the job (sample data in the first 2 lines; query begins at line #3):

SQL> with test (col) as
  2    (select 'This is the new ADHAR NUMBER 123456789989 this is the string 3456798983 from Customer Name like 345678 to a String .' from dual)
  3  select substr(col, 1, instr(col, 'ADHAR NUMBER') - 1) ||
  4         lpad('*', 60, '*') ||
  5         substr(col, instr(col, 'ADHAR NUMBER')   60) result
  6  from test;

RESULT
--------------------------------------------------------------------------------
This is the new ************************************************************ Cus
tomer Name like 345678 to a String .


SQL>

CodePudding user response：

Here is a solution that covers all possibilities (I think). Notice the different inputs in the WITH clause (which is not part of the solution - remove it, and use your actual table and column names in the query). This is how one should test their solutions - consider all possible cases, including NULL input, non-NULL input string that doesn't contain the "magic words", string that has the "magic words" right at the beginning, etc.

There is one important situation the solution does NOT address, namely when the exact substring 'ADHAR NUMBER' is not two full words, but it is part of longer words - for example 'BHADHAR NUMBERS'. In this case the output will look like 'BH****************' masking ADHAR NUMBER and the S after NUMBER and more characters, up to 60 total.

Note that the output string has the same length as the input. This is generally part of the definition of "masking".

with
  test (col) as (
    select 'This is the new ADHAR NUMBER 123456789989 this is the string ' ||
            '3456798983 from Customer Name like 345678 to a String.'
                                                          from dual union all
    select 'This string does not contain the magic words' from dual union all
    select 'ADHAR NUMBER 12345'                           from dual union all
    select 'Blah blah ADHAR NUMBER 1234'                  from dual union all
    select null                                           from dual union all
    select 'Another blah ADHAR NUMBER'                    from dual
  )
select case when pos > 0
            then
              substr(col, 1, pos - 1) ||
              rpad('*', least(60, length(col) - pos   1), '*') ||
              substr(col, pos   60)
            else col end as masked
from   (
         select col, instr(col, 'ADHAR NUMBER') as pos
         from   test
       )
;

MASKED                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This is the new ************************************************************ Customer Name like 345678 to a String.
This string does not contain the magic words
******************
Blah blah *****************

Another blah ************