9 Replies Latest reply: Nov 12, 2010 3:05 PM by Aketi Jyuuzou RSS

    String Compare

    user572405
      Pls see below the sample data
      CREATE TABLE STR_TEST
      (
        STR  VARCHAR2(50 BYTE)
      );
      commit;
      
      Insert into STR_TEST(STR) Values ('A,B,C');
      Insert into STR_TEST(STR) Values ('A,C,B');
      Insert into STR_TEST(STR) Values ('D,C,A');
      Insert into STR_TEST(STR) Values ('C,A,B');
      Insert into STR_TEST(STR) Values ('A,B');
      Insert into STR_TEST(STR) Values ('A,B,C,D');
      commit;
      I have to write a SQL which will compare the input string 'A,B,C' to the column STR in the above table and mark if both strings are same irrespective of the order. So, the input string matched to only the 1st 2 rows in the table:
      STR       Input_STR         Match
      ----------------------------------
      A,B,C      A,B,C                1
      A,C,B      A,B,C                1
      C,A,B      A,B,C                1
      D,C,A      A,B,C                0
      A,B        A,B,C                0
      A,B,C,D    A,B,C                0 
      Thank you.
        • 1. Re: String Compare
          odie_63
          What is your database version (select * from v$version)?
          • 2. Re: String Compare
            Tubby
            select  
              original_str
            from
            (
              select 
                original_str,
                listagg(split_val, ',') within group ( order by split_val asc) as ordered_list
              from
              (
                select 
                  s.str                                             as original_str,
                  regexp_substr(s.str, '[^,]+', 1, t.column_value)  as split_val
                from  
                  str_test s, 
                  table(cast(multiset(select level from dual connect by level <= length (regexp_replace(s.str, '[^,]+'))  + 1 ) as sys.odcinumberlist)) t    
              )
              group by original_str
            )
             19  where 'A,B,C' = ordered_list  
             20  /
            
            ORIGINAL_STR
            --------------------------------------------------
            A,B,C
            A,C,B
            C,A,B
            
            3 rows selected.
            
            TUBBY_TUBBZ?
            Requires version 10+ for the REGEXP stuff ...and 11.2 for LISTAGG (you can replace that with any string aggregation technique found here ... http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php ).

            This also assumes you can order (alphabetically) your input string ... i didn't bother doing that, but you could use the same technique and select the parameter from dual if you needed to do that as well.
            • 3. Re: String Compare
              odie_63
              An 11.2 solution :
              Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
              Connected as dev
               
              SQL> var in_str varchar2(50)
              SQL> exec :in_str := 'A,B,C';
               
              PL/SQL procedure successfully completed
               
              SQL> 
              SQL> SELECT str,
                2         :in_str input_str,
                3         xmlcast(
                4          xmlquery('if ( deep-equal(for $i in ora:tokenize($s,",") order by $i return $i,
                5                                    for $i in ora:tokenize($is,",") order by $i return $i) ) then 1 else 0'
                6                   passing t.str as "s",
                7                           :in_str as "is"
                8                   returning content) as number(1)
                9         ) match
               10  FROM str_test t
               11  ;
               
              STR        INPUT_STR   MATCH
              ---------- ----------- -------
              A,B,C      A,B,C             1
              A,C,B      A,B,C             1
              D,C,A      A,B,C             0
              C,A,B      A,B,C             1
              A,B        A,B,C             0
              A,B,C,D    A,B,C             0
               
              6 rows selected
              • 4. Re: String Compare
                Tubby
                odie_63 wrote:
                An 11.2 solution :
                Neat :)
                • 5. Re: String Compare
                  MichaelS
                  Or
                  SQL> var str varchar2(10)
                  
                  SQL> exec :str := 'A,B,C'
                  PL/SQL procedure successfully completed.
                  
                  SQL> select str,
                         :str str2,
                         extractvalue (
                           dbms_xmlgen.getxmltype (
                                'select case when sys.dbms_debug_vc2coll('''|| replace (:str, ',', ''',''')
                             || ''') = sys.dbms_debug_vc2coll(''' || replace (str, ',', ''',''')
                             || ''') then 1 else 0 end x from dual'), '//X/text()') x
                    from str_test
                  /
                  STR        STR2       X         
                  ---------- ---------- ----------
                  A,B,C      A,B,C      1         
                  A,C,B      A,B,C      1         
                  D,C,A      A,B,C      0         
                  C,A,B      A,B,C      1         
                  A,B        A,B,C      0         
                  A,B,C,D    A,B,C      0         
                  
                  6 rows selected.
                  • 6. Re: String Compare
                    odie_63
                    MichaelS wrote:
                    Or
                    Good one.
                    Didn't remember we can directly compare collections (nested tables).
                    • 7. Re: String Compare
                      Aketi Jyuuzou
                      If length of all string are equal,You can use LTrim ;-)
                      with STR_TEST(STR) as(
                      select 'A,B,C'   from dual union all
                      select 'A,C,B'   from dual union all
                      select 'C,A,B'   from dual union all
                      select 'D,C,A'   from dual union all
                      select 'A,B'     from dual union all
                      select 'A,B,C,D' from dual)
                      select STR,
                      case when LTrim(STR,'A,B,C') is null
                            and LTrim('A,B,C',STR) is null
                           then 1 else 0 end as Match
                      from STR_TEST;
                      
                      STR      MATCH
                      -------  -----
                      A,B,C        1
                      A,C,B        1
                      C,A,B        1
                      D,C,A        0
                      A,B          0
                      A,B,C,D      0
                      I have used below math logic B-)
                      *There are 2 sets.
                      *And both set contain the other set.
                      *Then these 2 sets are same.

                      There is sililar logic
                      Query solution needed
                      • 8. Re: String Compare
                        MaximDemenko
                        The result of this solution will differ however from other provided depending on presence of duplicates in the original string - like 'A,B,B,C' ( both approaches may have sense though).

                        Best regards

                        Maxim
                        • 9. Re: String Compare
                          Aketi Jyuuzou
                          I like recursive with clause B-)
                          with STR_TEST(STR) as(
                          select 'A,B,C'   from dual union all
                          select 'A,C,B'   from dual union all
                          select 'C,A,B'   from dual union all
                          select 'D,C,A'   from dual union all
                          select 'A,B'     from dual union all
                          select 'A,B,C,D' from dual),
                          rec(STR,extractStr,LV) as(
                          select STR,RegExp_Substr(STR,'[^,]+',1,1),1
                            from STR_TEST
                          union all
                          select STR,RegExp_Substr(STR,'[^,]+',1,LV+1),LV+1
                            from rec
                           where LV+1 <= RegExp_Count(STR,'[^,]+'))
                          select STR,
                          case STR when ListAgg(extractStr,',') within group(order by STR)
                                   then 1 else 0 end as Match
                            from rec
                          group by STR
                          order by STR;