1 2 Previous Next 19 Replies Latest reply: Jun 19, 2013 11:57 AM by 02575375-6bab-43f1-9072-6462c30556ba RSS

    REGEXP_REPLACE - Replace every occurrence of the letter in the pattern

    Karthick_Arp
      I need a regular expression query to do the following

      Input
      '[karthick] account number is [10000010101] and my pin is [1919128]'
      Output
      '######## account number is ########### and my pin is #######'
      In short any thing that is between [] should be replaced with #

      I tried this.
      SQL> with t
        2  as
        3  (
        4     select '[karthick] account number is [10000010101] and my pin is [1919128]' str
        5       from dual
        6  )
        7  select str, regexp_replace(str,'\[[[:alnum:]]+\]','#') str1
        8    from t
        9  /
      
      STR
      ------------------------------------------------------------------
      STR1
      --------------------------------------------------------------------------------
      [karthick] account number is [10000010101] and my pin is [1919128]
      # account number is # and my pin is #
      But i want all the letters inside [] to be replaced with #.


      Thanks,
      Karthick.
        • 1. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
          BluShadow
          It will be tricky doing it character for character, but if you can put up with a general substitution then you can do something like...
          SQL> ed
          Wrote file afiedt.buf
          
            1  with t as (select '[karthick] account number is [10000010101] and my pin is [1919128]' as txt from dual)
            2  --
            3  select regexp_replace(txt,'\[[^ ]+\]','##########') as txt
            4* from t
          SQL> /
          
          TXT
          ----------------------------------------------------------------
          ########## account number is ########## and my pin is ##########
          
          SQL>
          • 2. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
            Karthick_Arp
            this is nothing much different from what i already have
            SQL> with t
              2  as
              3  (
              4     select '[karthick] account number is [10000010101] and my pin is [1919128]' str
              5       from dual
              6  )
              7  select str, regexp_replace(str,'\[[[:alnum:]]+\]','########') str1
              8    from t
              9  /
            
            STR
            ------------------------------------------------------------------
            STR1
            --------------------------------------------------------------------------------
            [karthick] account number is [10000010101] and my pin is [1919128]
            ######## account number is ######## and my pin is ########
            i am banging my head for a wile for this. i hope some one can find a solution for this.
            • 3. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
              BluShadow
              Karthick_Arp wrote:
              this is nothing much different from what i already have
              I know.

              If you know that there are a fixed number of things to replace in the string then you could do it with multiple replaces...
              SQL> ed
              Wrote file afiedt.buf
              
                1  with t as (select '[karthick] account number is [10000010101] and my pin is [1919128]' as txt from dual)
                2  --
                3  select regexp_replace(
                4           regexp_replace(
                5             regexp_replace(txt,'\[[[:alnum:]]+\]',lpad('#',length(regexp_substr(txt,'\[[[:alnum:]]+\]')),'#'),1,1)
                6             ,'\[[[:alnum:]]+\]',lpad('#',length(regexp_substr(txt,'\[[[:alnum:]]+\]',1,2)),'#'),1,1)
                7           ,'\[[[:alnum:]]+\]',lpad('#',length(regexp_substr(txt,'\[[[:alnum:]]+\]',1,3)),'#'),1,1)
                8         as txt
                9* from t
              SQL> /
              
              TXT
              ------------------------------------------------------------------
              ########## account number is ############# and my pin is #########
              
              SQL>
              • 4. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
                Peter Gjelstrup
                BluShadow wrote:
                ... if you can put up with a general substitution then you can do something like...
                Dependant on what the actual problem is, this could even prove to be better since it would also disguise the length of what is being anonymized.

                Just a thought
                Peter
                • 5. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
                  BluShadow
                  Peter Gjelstrup wrote:
                  BluShadow wrote:
                  ... if you can put up with a general substitution then you can do something like...
                  Dependant on what the actual problem is, this could even prove to be better since it would also disguise the length of what is being anonymized.

                  Just a thought
                  Peter
                  A thought that had crossed my mind too. ;)
                  • 6. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
                    Solomon Yakobson
                    I'm afraid you can't do it with just regexp. Below is regexp+model solution:
                    with t as (
                               select  '[karthick] account number is [10000010101] and my pin is [1919128]' str
                                 from  dual
                              )
                    select  str
                      from  t
                      model
                        partition by(row_number() over(order by 1) rn)
                        dimension by(0 d)
                        measures(str,str str1)
                        rules
                          iterate (1000000000) until (str1[0] is null)
                          (
                           str1[0] = regexp_substr(str[0],'\[[[:alnum:]]+\]',1,1),
                           str[0]  = replace(str[0],str1[0],lpad('#',length(str1[0]),'#'))
                          )
                    /
                    
                    STR
                    ------------------------------------------------------------------
                    ########## account number is ############# and my pin is #########
                    SY.
                    • 7. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
                      469753
                      To get the one to one translation of characters between the square brackets, you could use the TRANSLATE function - it has the added benefit of allowing you to strip out the square bracket characters by not supplying a positionally corresponding replacement character.
                      Like this:
                      with t as (select '[karthick] account number is [10000010101] and my pin is [1919128]' str from dual)
                      select str
                            ,translate(substr(str,instr(str,'[',1,1),instr(str,']',1,1))
                                      ,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ[]'
                                      ,'##############################################################'
                                      )
                             ||substr(str,instr(str,']',1,1)+1,instr(str,'[',1,2)-instr(str,']',1,1)-1)
                             ||translate(substr(str,instr(str,'[',1,2),instr(str,']',1,2)-instr(str,'[',1,2)+1)
                                      ,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ[]'
                                      ,'##############################################################'
                                      )
                             ||substr(str,instr(str,']',1,2)+1,instr(str,'[',1,3)-instr(str,']',1,2)-1)
                             ||translate(substr(str,instr(str,'[',1,3),instr(str,']',1,3)-instr(str,'[',1,3)+1)
                                      ,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ[]'
                                      ,'##############################################################'
                                      ) Xlated
                      from t;
                      
                      STR
                      ------------------------------------------------------------------------------------------
                      XLATED
                      ------------------------------------------------------------------------------------------
                      [karthick] account number is [10000010101] and my pin is [1919128]
                      ######## account number is ########### and my pin is #######
                      
                      
                      1 row selected.
                      • 8. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
                        Sentinel
                        The way I see to do this is to treat it as a delimited string that you want to pull apart, then you can obfuscate individual parts of the string, then you can reassemble (e.g. stragg) the parts back together again. Here's an example, It can probably be broken fairly easily, and it's fairly drawn out. I would probably condense this code somewhat to do this for my self:
                        with dta as (select 1 id, '[karthick] account number is [10000010101] and my pin is [1919128]' str from dual
                          union all select 2 id, 'hey [sentinel] account number is [10010010101] and my pin is [9876543]' str from dual)
                        , dta1 as (select id
                                        , str
                                        , case substr(str,1,1) when '[' then 1 else 0 end offset
                                        , length(regexp_replace(ltrim(rtrim(str,'[]'),'[]'), '([^][]*)',''))+1 parts from dta)
                        , iter as (select level part from dual connect by level <= (select max(parts) from dta1))
                        , dta3 as (select id
                                        , part
                                        , offset
                                        , mod(part+offset,2) ind
                                        , regexp_substr(str, '([^][]+)', 1, part) str_part
                                     from dta1
                                     join iter on part <= parts)
                        , dta4 as (select id, part, offset, ind, case ind when 0 then rpad('x',length(str_part)+2, 'x') else str_part end str_part
                                     from dta3)
                        select id, max(replace(SYS_CONNECT_BY_PATH(str_part, '['),'[')) str
                          from dta4
                          connect by id = prior id
                            and part = prior part +1
                            start with part=1
                            group by id
                        
                        ID                     STR                                                                    
                        ---------------------- -----------------------------------------------------------------------
                        1                      xxxxxxxxxx account number is xxxxxxxxxxxxx and my pin is xxxxxxxxx     
                        2                      hey xxxxxxxxxx account number is xxxxxxxxxxxxx and my pin is xxxxxxxxx 
                        
                        2 rows selected
                        Edited by: Sentinel on Dec 1, 2008 4:36 PM
                        • 9. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
                          Aketi Jyuuzou
                          Hahaha I have used PL/SQL B-)
                          declare
                              str varchar2(200) := '[karthick] account number is [10000010101] and my pin is [1919128]';
                              InFlag boolean;
                              willOut varchar2(200);
                          begin
                              for i in 1..length(str) Loop
                                  if substr(str,i,1) = '[' then
                                      InFlag := true;
                                  elsif substr(str,i,1) = ']' then
                                      InFlag := false;
                                  elsif InFlag then
                                      willOut := willOut || '#';
                                  else
                                      willOut := willOut || substr(str,i,1);
                                  end if;
                              end Loop;
                              DBMS_Output.Put_Line(willOut);
                          end;
                          /
                          ######## account number is ########### and my pin is #######
                          • 10. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
                            Aketi Jyuuzou
                            Hahaha I have used JavaStoredProcedure ;-)
                            Regex which I have used is [^\[](?=[^\[]*\]) replace to #
                            
                            exec dbms_java.dropjava('JavaRegexSource');
                            exec dbms_java.dropjava('JavaRegex');
                            
                            CREATE or replace JAVA SOURCE NAMED JavaRegexSource as
                            public class JavaRegex {
                                public static String execRegex(String hiki){
                                    return hiki.replaceAll("[^\\[](?=[^\\[]*\\])","#");
                                }
                            }
                            /
                            
                            CREATE OR REPLACE Function ExecJavaRegex(hiki varchar2) return varchar2
                            AS LANGUAGE java NAME 'JavaRegex.execRegex(java.lang.String) return java.lang.String';
                            /
                            
                            conn test/test
                            col res1 for a70
                            
                            select ExecJavaRegex('[karthick] account number is [10000010101] and my pin is [1919128]') as res1
                            from dual;
                            
                            RES1
                            ------------------------------------------------------------------
                            [########] account number is [###########] and my pin is [#######]
                            • 11. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
                              MichaelS
                              Of course there's also a XML solution:
                              SQL>  with dta as (
                                select 1 id, '[karthick] account number is [10000010101] and my pin is [1919128]' str from dual union all 
                                select 2 id, '[michael] account number is [11110010010101] and my pin is [9876]' str from dual
                              )
                              --
                              --
                              select id, 
                                     t.column_value.extract('//text()').getstringval() str
                                from dta, 
                                     xmltable('element c {for $i in c/* return (ora:replace($i[name()="b"],".","#"),$i)[1]}'
                                              passing xmltype('<c><n>' || regexp_replace(str,'\[(.*?)\]','</n><b>\1</b><n>') || '</n></c>')) t
                              
                                 ID STR                                                                                       
                              ----- ------------------------------------------------------------------------------------------
                                  1  ######## account number is  ########### and my pin is  #######                           
                                  2  ####### account number is  ############## and my pin is  ####  
                              • 12. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
                                Peter Gjelstrup
                                I must get back to this thread when I'm older and wiser :-)
                                • 13. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
                                  731285
                                  My 2cts for the original question, enhanced by Peter would be ..
                                  with t
                                  as
                                  (
                                     select '[karthick] account number is [10000010101] and my pin is [1919128]' str
                                       from dual
                                  )
                                  select str, regexp_replace(str,'(^\[).*(\].*\[){1}.*(\].*\[){1}.*(\]$)','\1#\2#\3#\4') str1
                                    from t
                                  ;
                                  STR     STR1
                                  [karthick] account number is [10000010101] and my pin is [1919128]     [#] account number is [#] and my pin is [#]     
                                  • 14. Re: REGEXP_REPLACE - Replace every occurrence of the letter in the pattern
                                    BluShadow
                                    BobRomeo wrote:
                                    My 2cts for the original question, enhanced by Peter would be ..
                                    Apart from digging up a thread nearly a year old, what additional benefit does your answer provide over and above the answers already given?

                                    btw. your result could be more easily achieved with...
                                    SQL> ed
                                    Wrote file afiedt.buf
                                    
                                      1  with t
                                      2  as
                                      3  (
                                      4     select '[karthick] account number is [10000010101] and my pin is [1919128]' str
                                      5       from dual
                                      6  )
                                      7  select str, regexp_replace(str,'\[[^ ]+\]','[#]') str1
                                      8*   from t
                                    SQL> /
                                    
                                    STR
                                    ------------------------------------------------------------------
                                    STR1
                                    ---------------------------------------------------------------------------------------
                                    [karthick] account number is [10000010101] and my pin is [1919128]
                                    [#] account number is [#] and my pin is [#]
                                    1 2 Previous Next