7 Replies Latest reply: Nov 18, 2008 4:18 AM by Chief Wiggum RSS

    RegExp "NOT IN"

    Chief Wiggum
      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
          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
            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
              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
                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
                  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
                    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
                      Thanks.