4 Replies Latest reply: Dec 31, 2012 6:01 AM by padders RSS

    String Comparison

    887479
      11gR2 DB

      requirement is to find the strings in the below data - which has similar strings before and after the #
      with t as
      (
       select '123#123' str from dual union all
       select '111#111' str from dual union all
       select '456#457' str from dual
      )
      select *
      from t
      where ...
      
      Expected output
      --------
      123#123
      111#111
        • 1. Re: String Comparison
          Frank Kulash
          Hi,

          Here's one way:
          WITH     got_pound_pos     AS
          (
               SELECT     str
               ,     INSTR (str, '#')     AS pound_pos
               FROM     t
          )
          SELECT     str
          FROM     got_pound_pos
          WHERE     SUBSTR (str, 1, pound_pos - 1)
                = SUBSTR (str, pound_pos + 1)
          ;
          Edited by: Frank Kulash on Dec 31, 2012 6:15 AM

          Another way is to use regular expresssions:
          SELECT     str
          FROM     t
          WHERE     REGEXP_LIKE ( str
                        , '^(.+)#\1$'
                        )
          ;
          but this will be slower than using INSTR and SUBSTR.
          • 2. Re: String Comparison
            jeneesh
            One way..
            with t as
            (
             select '123#123' str from dual union all
             select '111#111' str from dual union all
             select '456#457' str from dual
            )
            select *
            from t
            where str = regexp_replace(str,'^([^#]+)(#)(.*)$','\1\2\1');
            • 3. Re: String Comparison
              Solomon Yakobson
              with t as (
                         select '123#123' str from dual union all
                         select '111#111' str from dual union all
                         select '456#457' str from dual
                        )
              select  *
                from  t
                where substr(str,1,instr(str,'#') - 1) = substr(str,instr(str,'#') + 1)
              /
              
              STR
              -------
              123#123
              111#111
              
              SQL> 
              SY.
              • 4. Re: String Comparison
                padders
                Using a back reference might be another possibility.
                WHERE REGEXP_LIKE (str, '^([^#]+)#\1$')