This discussion is archived
10 Replies Latest reply: Jan 27, 2011 9:51 AM by 684081 RSS

Regexp NOT

Chief Wiggum Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Aketi
  • 10. Re: Regexp NOT
    684081 Newbie
    Currently Being Moderated
    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;

Legend

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