CODEWITHSUNDEEP
oracle-databasecompare
Aditya
Aditya

Reputation: 165

Compare two comma-separated columns in two different oracle tables

I want to compare a comma-separated column in oracle table with another comma-separated column in a different table. If the all the values (within comma-separated column and irrespective of their order) on column one are contained within column two it should not return that row.

For example -

  1. Column 1 has a,b,c,def Column 2 has a,c,b

    This should not be returned as a,c and b in column 2 are contained within column 1 irrespective of their order.

  2. Column 1 has a,cl,owd Column 2 has a,owd,pp

    This row should be returned because column 2 has 'pp' which is not contained within Column 1.

  3. Column 1 has vvv,ccc,rr Column 2 has ccc,rr

    This row should not be returned.

Can I accomplish this in SQL ? Not expecting a procedure or function.

Thanks for all the help !!

Upvotes: 0

Views: 2724

Answers (1)

GWu
GWu

Reputation: 2787

First: I think your example doesn't match your question.

But concentrating on your question: If the all the values (within comma-separated column and irrespective of their order) on column one are contained within column two it should not return that row.

You need to parse the csv into a set (the regexp_substr part) and then check if the set from column two fully contains the set from column one (the minus part):

SELECT *
  FROM csv
 WHERE EXISTS
         (SELECT REGEXP_SUBSTR (col1, '[^,]+', 1, ROWNUM)
            FROM DUAL
           CONNECT BY ROWNUM <= LENGTH (col1) - LENGTH (REPLACE (col1, ',')) + 1
          MINUS
          SELECT REGEXP_SUBSTR (col2, '[^,]+', 1, ROWNUM)
            FROM DUAL
          CONNECT BY ROWNUM <= LENGTH (col2) - LENGTH (REPLACE (col2, ',')) + 1
         )
;

See http://www.sqlfiddle.com/#!4/3cdb3/1 for working example.

Upvotes: 2

Related Questions