1 Reply Latest reply on Jan 9, 2020 1:22 AM by Gaz in Oz

    Regular Expression to change data

    3219506

      Hello Team,

       

      Warm wishes of Happy New Year to all of you.

       

      I have acolumn whose datatype could be VARCHAR(2) or CLOB. In the column we could have multiple lines of data. Now we have to replace every line of data with a constant string as below

       

       

       

       

       

       

       

       

       

      .

       

       

       

       

       

       

      B

       

       

       

       

       

       

       

       

      Before

      After

      Spoke to customers to get the documents.

      This data has been masked.

      Cheque is on hold at the request of customer due to fund issues.

      Chase the customer in November if no action taken

      This data has been masked.

      This data has been masked.

      Customer is in dispute regarding this application.

      A fraud investigation has been raised in the auditing department.

      Customer has been informed of progress

      This data has been masked.

      This data has been masked.

      This data has been masked.

       

       

      Now I am trying to achieve this using regular expressions in Oracle. Can someone help me with a solution.

       

      Thanks & Regards

        • 1. Re: Regular Expression to change data
          Gaz in Oz

          Here's an example simulating your data.

          SQL> ed

          Wrote file afiedt.buf

           

            1  with x (before) as (

            2     select to_clob('Spoke to customers to get the documents.') from dual union all

            3     select to_clob('Cheque is on hold at the request of customer due to fund issues.

            4  Chase the customer in November if no action taken') from dual union all

            5     select to_clob('Customer is in dispute regarding this application.

            6  A fraud investigation has been raised in the auditing department.

            7  Customer has been informed of progress') from dual

            8  )

            9  select before,

          10         regexp_replace(before, '^.*$', 'This data has been masked.', 1, 0, 'm') after

          11* from x

          SQL> /

           

          BEFORE                                             AFTER

          -------------------------------------------------- --------------------------------------------------

          Spoke to customers to get the documents.           This data has been masked.

           

          Cheque is on hold at the request of customer due   This data has been masked.

          to fund issues.                                    This data has been masked.

          Chase the customer in November if no action taken

           

          Customer is in dispute regarding this application. This data has been masked.

          A fraud investigation has been raised in the       This data has been masked.

          auditing department.                               This data has been masked.

          Customer has been informed of progress

           

          3 rows selected.

           

          SQL>

          REGEXP_REPLACE()

          Note the  match_param argument of 'm' :

          'm' treats the source string as multiple lines. Oracle interprets the caret ( ^) and dollar sign ($) as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, then Oracle treats the source string as a single line.