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

REGEXP_REPLACE - Replace every occurrence of the letter in the pattern

Karthick_Arp Guru
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points