Skip to Main Content
  • Questions
  • Regexp_Replace Help- Column Level Data Masking

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Soumik.

Asked: September 04, 2018 - 1:18 pm UTC

Last updated: September 08, 2018 - 10:24 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

I have this requirement but regexp_replace is behaving odd here. It gives me all NULL

Requirement for function- Suppose a number 9845-089160
• function logic should convert one digit at a time and not entire number in one go.
• First 4 digit : Out from conversion
• In Last 6 Digit : Two digits 0 & 9 should be kept out from conversion (if found).

Below package is not giving what i intended it should.

create or replace PACKAGE BODY pkg_test2 AS

    FUNCTION digimask (
        nrr_number_low IN VARCHAR2
    ) RETURN VARCHAR2 AS
        m_len         NUMBER := 0;
        m_no_masked   VARCHAR2(10) := NULL;
        m_no          VARCHAR2(10);
    BEGIN
        dbms_output.put_line('ORIGINAL MOBILE NUMBER: ' || nrr_number_low);
        m_no_masked := regexp_replace(substr(m_no,1,4),'0|1|2|3|4|5|6|7|8|9','*')
                       || regexp_replace(substr(m_no,5,6),'1|2|3|4|5|6|7|8','*');

        dbms_output.put_line('MASKED MOBILE NUMBER: ' || m_no_masked);
        RETURN m_no_masked;
    END digimask;

END;

and Connor said...

Your regexp is fine...but your PLSQL logic is not :-)

SQL> with t as
  2  ( select '9845-089160' m_no from dual )
  3  select
  4  regexp_replace(substr(m_no,1,4),'0|1|2|3|4|5|6|7|8|9','*')
  5                         || regexp_replace(substr(m_no,5,6),'1|2|3|4|5|6|7|8','*')
  6  from t;

REGEXP_REP
----------
****-0*9**

1 row selected.

SQL>
SQL>
SQL> create or replace
  2  FUNCTION digimask (
  3      nrr_number_low IN VARCHAR2
  4  ) RETURN VARCHAR2 AS
  5      m_len         NUMBER := 0;
  6      m_no_masked   VARCHAR2(10) := NULL;
  7      m_no          VARCHAR2(10);
  8  BEGIN
  9      dbms_output.put_line('ORIGINAL MOBILE NUMBER: ' || nrr_number_low);
 10      m_no_masked := regexp_replace(substr(m_no,1,4),'0|1|2|3|4|5|6|7|8|9','*')
 11                     || regexp_replace(substr(m_no,5,6),'1|2|3|4|5|6|7|8','*');
 12
 13      dbms_output.put_line('MASKED MOBILE NUMBER: ' || m_no_masked);
 14      RETURN m_no_masked;
 15  END digimask;
 16  /

Function created.

SQL>
SQL> set serverout on
SQL> select digimask('9845-089160') from dual;

DIGIMASK('9845-089160')
----------------------------------------------------------------------------------------------------


1 row selected.

ORIGINAL MOBILE NUMBER: 9845-089160
MASKED MOBILE NUMBER:
SQL>
SQL> create or replace
  2  FUNCTION digimask (
  3      nrr_number_low IN VARCHAR2
  4  ) RETURN VARCHAR2 AS
  5      m_len         NUMBER := 0;
  6      m_no_masked   VARCHAR2(22) := NULL;
  7      m_no          VARCHAR2(22) := nrr_number_low;
  8  BEGIN
  9      dbms_output.put_line('ORIGINAL MOBILE NUMBER: ' || nrr_number_low);
 10      m_no_masked := regexp_replace(substr(m_no,1,4),'0|1|2|3|4|5|6|7|8|9','*')
 11                     || regexp_replace(substr(m_no,5,6),'1|2|3|4|5|6|7|8','*');
 12
 13      dbms_output.put_line('MASKED MOBILE NUMBER: ' || m_no_masked);
 14      RETURN m_no_masked;
 15  END digimask;
 16  /

Function created.

SQL>
SQL> set serverout on
SQL> select digimask('9845-089160') from dual;

DIGIMASK('9845-089160')
----------------------------------------------------------------------------------------------------
****-0*9**

1 row selected.

ORIGINAL MOBILE NUMBER: 9845-089160
MASKED MOBILE NUMBER: ****-0*9**


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Regexp Help

Soumik B, September 05, 2018 - 7:40 am UTC

Thanks Tom for your help and support.

As per requriment the First 4 digit should be there and it also does not need a conversion, along with 0 and 9.

ORIGINAL MOBILE NUMBER: 9845-089160

MASKED MOBILE NUMBER: ****-0*9** (Currently this is the output)

EXPECTED MOBILE NUMBER : 9845-0*9**0 ( This is what business is expecting)


Chris Saxon
September 05, 2018 - 12:41 pm UTC

Then don't apply the regexp to the first four digits...

version

Racer I., September 05, 2018 - 10:37 am UTC

Hi,

with t as
( select '9845-089160' m_no from dual )
select m_no, substr(m_no, 1, instr(m_no, '-')) || regexp_replace(substr(m_no, instr(m_no, '-') + 1),'1|2|3|4|5|6|7|8','*')
from t

Also : you don't need the m_no-variable in your function. Just use nrr_number_low directly.

Given : function logic should convert one digit at a time and not entire number in one go.
Therefore shouldn't you do this in a char by char loop;)

I think tegexp-wizards could do it without substr/instr. In fact without any extra function at all.

regards,
Chris Saxon
September 05, 2018 - 12:40 pm UTC

Well rexexp_replace does have a parameter to define the start position for the search. So I doubt the regex needs to be that complicated ;)

Solved::: Regexp_Replace Help- Column Level Data Masking

Soumik B, September 06, 2018 - 9:54 am UTC

Thanks Tom it was awesome, for your help...
Connor McDonald
September 08, 2018 - 10:24 am UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library