This discussion is archived
4 Replies Latest reply: Dec 31, 2012 4:01 AM by padders RSS

String Comparison

887479 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    Using a back reference might be another possibility.
    WHERE REGEXP_LIKE (str, '^([^#]+)#\1$')

Legend

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