10 Replies Latest reply: Jan 27, 2011 11:51 AM by 684081 RSS

    Regexp NOT

    Chief Wiggum
      Hello,

      I know that regexp_replace('abc1234abc','[^[:digit:]]',null) will give me a string replacing anything that is NOT a number wiyth NULL

      select regexp_replace('abc1234abc56def','[^[:digit:]]',null) from dual ---> 123456

      Now, if I change the question slightly to "replace eveything that is ** not ** a digit followed by an alpha with null how cna I do that

      i.e, I want the answer to be

      select regexp_replace('abc1234abc56def','[^[:digit:][:alpha:]]',null) from dual ---> I.E i WANT "4A" AND "6D" TO BE INTACT

      HOW ??

      THANKS IN ADVANCE
        • 1. Re: Regexp NOT
          435083
          Hi,

          Check the query below :

          select regexp_replace('abc1234abc56def','.*(\[0-9\]\[a-z\]).*(\[0-9\]\[a-z\]).*','\1 \2') from dual

          ~Vinod

          Edited by: vsugur on Nov 18, 2008 4:43 PM

          Edited by: vsugur on Nov 18, 2008 4:44 PM
          • 2. Re: Regexp NOT
            BluShadow
            vsugur wrote:
            Hi,

            Check the query below
            :

            select regexp_replace('abc1234abc56def','.*([0-9][a-z]).*([0-9][a-z]).*','\\1 \\2') from dual
            ~Vinod

            Edited by: vsugur on Nov 18, 2008 4:43 PM
            I think you wanted to show that with code tags around it...
            SQL> ed
            Wrote file afiedt.buf
            
              1* select regexp_replace('abc1234abc56def','.*([0-9][a-z]).*([0-9][a-z]).*','\1\2') from dual
            SQL> /
            
            REGE
            ----
            4a6d
            • 3. Re: Regexp NOT
              BluShadow
              vsugur wrote:
              Hi,

              Check the query below :
              Your solution also only deals with two occurrences of digit:alpha...
              SQL> ed
              Wrote file afiedt.buf
              
                1* select regexp_replace('abc1234abc56def45fdf','.*([0-9][a-z]).*([0-9][a-z]).*','\1\2') from dual
              SQL> /
              
              REGE
              ----
              6d5f
              Ooops what happend to "4a"?
              • 4. Re: Regexp NOT
                BluShadow
                Unfortunately, regular expressions aren't brilliant at doing the NOT that we so often require.

                A workaround for this particular issue would be...
                SQL> ed
                Wrote file afiedt.buf
                
                  1  with txt as (select 'abc1234abc56def5f3f' as txt from dual)
                  2  --
                  3  select replace(sys_connect_by_path(txt,','),',') as txt
                  4  from (
                  5    select rownum rn, regexp_substr(txt,'[0-9][[:alpha:]]',1,rownum) txt
                  6    from txt
                  7    connect by rownum <= (length(txt)-length(regexp_replace(txt,'[0-9][[:alpha:]]'))+2)/2
                  8    )
                  9  where connect_by_isleaf = 1
                 10  connect by rn = prior rn + 1
                 11* start with rn = 1
                SQL> /
                
                TXT
                -----------------------------------------------------------------------------------------------
                4a6d5f3f
                
                SQL>
                which deals with any number of occurrences within the string.
                • 5. Re: Regexp NOT
                  Aketi Jyuuzou
                  previously word
                  return all the occurrences of a substring
                  RegExp "NOT IN"

                  I used method which I learn from CD B-)
                  Hahaha today I say it twice :8}
                  select RegExp_Replace(Val,'([0-9][a-z])|.','\1') as aaa
                  from (select 'abc1234abc56def' as Val from dual);
                  
                  aaa
                  ----
                  4a6d
                  Of course you can use below one ;-)
                  select 
                  RegExp_Substr(Val,'[0-9][a-z]',1,1) as "first",
                  RegExp_Substr(Val,'[0-9][a-z]',1,2) as "second",
                  RegExp_Substr(Val,'[0-9][a-z]',1,3) as "third"
                  from (select 'abc1234abc56def' as Val from dual);
                  
                  fi  se  third
                  --  --  ----
                  4a  6d  null
                  • 6. Re: Regexp NOT
                    Chief Wiggum
                    Sorry Guys, I am much thankful for your efforts but the whold point of my question is

                    how to say in regexp - NOT

                    i.e, how can I select something which is the reverse of a given string ? say from a string "abcdef" I want to select "everythogn other than "bcd"

                    ie, the "string bcd" not just b or c or d but the string as a whole "bcd" - how can you negate it in a regexp ???
                    • 7. Re: Regexp NOT
                      BluShadow
                      Chief Wiggum wrote:
                      ie, the "string bcd" not just b or c or d but the string as a whole "bcd" - how can you negate it in a regexp ???
                      With the exception of negating individual digits/characters you can't negate a search string in regexp. You have to turn it around to what you do want to look for, and then maybe use regexp_replace rather than regexp_substr or the other way around depending on what you want to do with the things you find.

                      It's a pain I know, and something I keep coming across myself, but that's regular expressions for you.
                      • 8. Re: Regexp NOT
                        Aketi Jyuuzou
                        If string Length is 1,
                        we can use charClass like below one.
                        select regexp_replace('abcdefg','[bcd]') as aaa from dual;
                        
                        AAA
                        ----
                        aefg
                        If string Length is more than 1,
                        we cannot use it.

                        However we can use method of CD.
                        It can derive result which we want B-)
                        • 9. Re: Regexp NOT
                          Chief Wiggum
                          Thanks Aketi
                          • 10. Re: Regexp NOT
                            684081
                            To replace any characters that are NOT alphanumeric OR a dash OR an underscore, I used the following:

                            select regexp_replace('sour#ce *str()-ing2', '[^[:alnum:]|_|-]+', '') from dual;