Search code examples
regexoracle-databasemasking

Using Oracle Regular Expression - Masking based on pattern

Cleaning up ,

With Oracle 11g PL/SQL, for below query, can I get the capture groups' positions (something like what Matcher.start() provides in java).

    `select regexp_replace('1234bankzone1234', '^..(.*)bank(zone).(.*)..$', '\2') from dual`

Result should look like : "zone", 9(start of text "zone").

The bigger problem I was trying to solve is to mask data like account number using patterns like '^.....(.*)..$' (this pattern can vary depending on installation).

Solution

  • 1: Reverse pattern using this

    regexp_replace( regexp_replace( regexp_replace( regexp_replace( regexp_replace( regexp_replace( regexp_replace( regexp_replace( regexp_replace( pattern, '(\()', '\1#') , '(\))', '#\1') , '\(#', ')#') , '\^\)#', '^') , '#\)\$', '$') , '#\)', '(#') , '#', '') , '\^([^\(]+\))', '^(\1') , '\(([^\)]+)\$', '(\1)$');

    So, "^(.)..(.).$"; becomes "^.(..).(.)$";

    2: Use this to bulk collect index and count of capture groups within both patterns

    SELECT REGEXP_instr(pattern, '\(.*?\)+', 1, LEVEL) bulk collect into posCapture FROM v CONNECT BY LEVEL <= REGEXP_COUNT(pattern, '\(.*?\)');

    3: Match both patterns against the text-to-be-masked. Merge them by the order found in step 2.

    select regexp_replace(v_src, pattern, '\' || captureIndex) into tempStr from dual;