This discussion is archived
7 Replies Latest reply: Nov 18, 2008 2:18 AM by Chief Wiggum RSS

RegExp "NOT IN"

Chief Wiggum Newbie
Currently Being Moderated
Hello,

From the query given below, I need to do just get "this" [i.e, I need to just  replace whatever is NOT "this" to NULL).

when I tried it like given below, it considers "t" "h" "i" and "s" as separate and replaces each and every one of them - can you please help ?


with q1 as
      ( select 'test_this_23stay_count' str from dual union
      select 'say_thisandthat_s13et_count' from dual  )
select str,
      regexp_replace(str,'[^(this)]','') new_str
from q1

regards,

Raj
  • 1. Re: RegExp "NOT IN"
    Sentinel Pro
    Currently Being Moderated
    The circumflex (^) at the beginning of your regular expression tells it you want to only match the word "this" if it occurs at the beginning of the string. Why not try the regexp_substr function instead here's code with both a modified regexp_replace as well as an example using regexp_substr:
    WITH q1 AS
      ( SELECT 'test_this_23stay_count' str FROM dual
        UNION ALL
        SELECT 'say_thisandthat_s13et_count' FROM dual
        UNION ALL
        SELECT 'say_that_s13et_count' FROM dual
      )
     SELECT str
          , regexp_substr( str, 'this' ) new_sub_str 
          , regexp_replace(str, '(.+)(this)(.+)','\2') new_rep_str
       FROM q1;
    Note that the substring version returns only the word this when it exists otherwise it returns null. The replace version returns the word this when it exists otherwise it returns the original string.

    Edited by: Sentinel on Nov 17, 2008 9:52 AM
  • 2. Re: RegExp "NOT IN"
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Do you want an expression that
    returns 'this' if str contains 'this', and
    returns NULL otherwise?

    Then:
    CASE
        WHEN  str LIKE '%this%'
        THEN  'this'
        ELSE  NULL
    END
    Or you can make this more gerenal, like this:
    CASE
        WHEN  str LIKE '%' || :target_str || '%'
        THEN  :target_str
        ELSE  NULL
    END
    Use a column name or a substitution variable instead of a bind variable, if you want.

    Edited by: Frank Kulash on Nov 17, 2008 12:51 PM
    Better yet, REGEXP_SUBSTR (str, :target_str), like Sentinel suggested.
  • 3. Re: RegExp "NOT IN"
    Chief Wiggum Newbie
    Currently Being Moderated
    Sorry. What I want is to give me all occurences of "this" from the string

    Like for e.g, if the string is "thisthatthis" I need "thisthis" returned.
    Thats why I was tryign to do the "NOT" logic in Regexp - is there NOT logic in REGEXP ?
  • 4. Re: RegExp "NOT IN"
    Chief Wiggum Newbie
    Currently Being Moderated
    Thanks for your prompt reply - what I need is ALL occurences of the string that I am searching for

    in this case all occurences of "this" (ive changed the
    'say_thisandthat_this13et_count' to include another this for testing - but this returnes only one "this"

    WITH q1 AS
    ( SELECT 'test_this_23stay_count' str FROM dual
    UNION ALL
    SELECT 'say_thisandthat_this13et_count' FROM dual
    UNION ALL
    SELECT 'say_that_s13et_count' FROM dual
    )
    SELECT str
    , regexp_substr( str, 'this' ) new_sub_str
    , regexp_replace(str, '(.+)(this)(.+)','\2') new_rep_str
    FROM q1;
  • 5. Re: RegExp "NOT IN"
    Sentinel Pro
    Currently Being Moderated
    Then I guess you want the final example in this code:
    WITH q1 AS ( SELECT 'test_this_23stay_count' str FROM dual
       UNION ALL SELECT 'say_thisandthat_this13et_count' FROM dual
       UNION ALL SELECT 'say_that_s13et_count' FROM dual
    )
     SELECT str
          , regexp_substr( str, 'this' ) new_sub_str
          , regexp_replace( str, '(.+)(this)(.+)', '\2' ) new_rep_str
          , rpad( 'this'
                , ( LENGTH( str ) - LENGTH( regexp_replace( str, 'this', NULL ) ) )
                , 'this' ) this
       FROM q1;
  • 6. Re: RegExp "NOT IN"
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    I used method which I learn from CD B-)

    previously word
    return all the occurrences of a substring
    col new_sub_str for a10
    col new_rep_str for a20
    col this for a10
    
    WITH q1 AS ( SELECT 'test_this_23stay_count' str FROM dual
       UNION ALL SELECT 'say_thisandthat_this13et_count' FROM dual
       UNION ALL SELECT 'say_that_s13et_count' FROM dual
    )
     SELECT str
          , regexp_substr( str, 'this' ) new_sub_str
          , regexp_replace( str, '.+(this).+', '\1' ) new_rep_str
          , RegExp_Replace(str,'(this)|.','\1') this
       FROM q1;
    
    STR                             NEW_SUB_ST  NEW_REP_STR           THIS
    ------------------------------  ----------  --------------------  ---------
    test_this_23stay_count          this        this                  this
    say_thisandthat_this13et_count  this        this                  thisthis
    say_that_s13et_count            null        say_that_s13et_count  null
  • 7. Re: RegExp "NOT IN"
    Chief Wiggum Newbie
    Currently Being Moderated
    Thanks.

Legend

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