Compare 2 comma-separated columns in two different oracle tables

Question

I want to compare columnn-A of Table-1 with Column B of Table-2. both have comma separated values.
In my case if all the values in column B exists in column A, I have to return TRUE
ELSE FALSE. Please advise. for example:

1.Column A has a,b,c,def Column B has a,c,b its should return TRUE
2. Column 1 has vvv,ccc,rr Column 2 has ccc,rr, 125 it should return FALSE
3. Column 1 has null and Column 2 has null, it should return TRUE

Please help

The current table design breaks first normal form, each value should be atomic *(a separate field or row for each value, rather than stuffing them in to a single string)*. I strongly recommend fixing the design. (There is a world of hurt with this design.) — MatBailie, May 24 '17 at 23:41
Your example breaks not only good design principles, but the basic rules of three-value logic. NULL is never equal to anything, including NULL. Any comparison with NULL returns neither TRUE or FALSE, but NULL. Basically what you've got here is what is commonly referred to as "a disaster in the making". All those relational design rules, and all that stuff about "normalization" and stuff? Yeah - it's there to prevent you from making this kind of mistake. DO NOT DO THIS! (And if this is a class exercise you have my permission to tell your instructor it's a very bad example. Best of luck). — Bob Jarvis - Слава Україні, May 25 '17 at 01:32
@BobJarvis In Oracle NULL also stands for the empty string, an ''=='' or ''!='a' makes sense. — miracle173, May 25 '17 at 04:52
@miracle173 I think that's the other way around. In Oracle, a NULL String is represented by the Empty String. — MatBailie, May 25 '17 at 10:35
@MatBailie I didn't talk how it is represented internal but the output of `select nvl('','is_null') from dual;` is `is_null` and `select * from dual where ''='' ;` returns no rows in contrast to `select * from dual where 'a'='a'` which returns a row. — miracle173, May 25 '17 at 10:45
is this a homework question or is actually a problem you encounter on a working system? — miracle173, May 25 '17 at 11:02
@MatBailie - in Oracle NULL and the empty string are the same thing. Any attempt to put a zero-length string ('') into a VARCHAR2 field results in a NULL value being stored in the field. At present this also applies to VARCHAR fields but Oracle has indicated that in future the semantics of VARCHAR (but not the Oracle-proprietary VARCHAR2 type) **may** be altered to match the ANSI definition, which requires that zero-length strings be treated differently than NULL. — Bob Jarvis - Слава Україні, May 25 '17 at 14:55

score 1 · Answer 1 · answered Jul 12 '18 at 05:16

It can be done with this SQL:

    with t1 as( select '1111,1985,433435,5765' as colA from dual union all
                select '232323,7777,8888,9999' as colA from dual union all
                select '111,2000,433435,5765' as colA from dual),
      t2 as ( select '1111,466576,24323' as colB from dual union all
              select '11111,1985,435657' as colb from dual union all
              select '2222,232323,24989323' as colb from dual )

    select colA from t1 where exists(
select 1 from t2 where REGEXP_COUNT(','||t1.colA||',' , ',('||REPLACE(t2.colB, ',' , '|') || '),' ) > 0)

score 0 · Answer 2 · answered May 25 '17 at 06:13

0

Hope this snippet helps.

SELECT DECODE(NULLIF(
  (SELECT LISTAGG(x.col_val,',') WITHIN GROUP(
  ORDER BY x.col_val)intrsctnVal
  FROM
    (SELECT SUBSTR(REPLACE(COL2,',',''),LEVEL,1) col_val
    FROM
      (SELECT 'a,b,c' AS COL2 FROM DUAL
      )B
      CONNECT BY level <= regexp_count(col2,',')+1
    INTERSECT
    SELECT SUBSTR(REPLACE(COL1,',',''),LEVEL,1) col_val
    FROM
      (SELECT 'a,b,c,d' AS COL1 FROM DUAL
      )A
      CONNECT BY LEVEL <= REGEXP_COUNT(COL1,',')+1
    )X
  ),
  (SELECT 'a,b,c' AS COL2 FROM DUAL
  )),NULL,'TRUE','FALSE') MATCH
FROM DUAL;

answered May 25 '17 at 06:13

Avrajit Roy

3,233
1
13
25

1

Now do that where the values come from two tables being joined together? It's do-able but messy, complicated and slow (the same join repeated multiple times). – MatBailie May 25 '17 at 10:34
We always hope that it will help but some additional notes may drastically increase the chances that it actually will help. – miracle173 May 25 '17 at 11:57
thanks a lot, sorry did not see it earlier, my appologies. – Arch Dec 11 '17 at 19:08

saphsys · Answer 3 · 2017-06-05T12:30:40.727

Eventually, I've got it ) It was an interesting task for me to solve it using only SQL:

         B          N SORTED                                                                           BOOL
---------- ---------- -------------------------------------------------------------------------------- -----
         1          1 [a],[b],[c]                                                                      true
         1          2 [ccc],[rr],[vvv]                                                                 false
         1          3 []                                                                               true

1 step: union two tables:

SQL> with
  2  t1 as (
  3    select 1 n, 'c,b,a' c from dual
  4    union all
  5    select 2 n, 'vvv,ccc,rr' c from dual
  6    union all
  7    select 3 n, null c from dual),
  8  t2 as (
  9    select 1 n, 'a,b,c' c from dual
 10    union all
 11    select 2 n, 'ccc,rr,125' c from dual
 12    union all
 13    select 3 n, null c from dual),
 14  t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2)
 15  select * from t12
 16  /

         B          N C
---------- ---------- ----------
         1          1 c,b,a
         1          2 vvv,ccc,rr
         1          3 
         2          1 a,b,c
         2          2 ccc,rr,125
         2          3 

6 rows selected

2 step: count separators:

SQL> with
  2  t1 as (
  3    select 1 n, 'c,b,a' c from dual
  4    union all
  5    select 2 n, 'vvv,ccc,rr' c from dual
  6    union all
  7    select 3 n, null c from dual),
  8  t2 as (
  9    select 1 n, 'a,b,c' c from dual
 10    union all
 11    select 2 n, 'ccc,rr,125' c from dual
 12    union all
 13    select 3 n, null c from dual),
 14  t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2),
 15  t as (select b, n, c, nvl(length(regexp_replace(c, '[^,]+')) + 1, 1) cnt from t12)
 16  select * from t
 17  /

         B          N C                 CNT
---------- ---------- ---------- ----------
         1          1 c,b,a               3
         1          2 vvv,ccc,rr          3
         1          3                     1
         2          1 a,b,c               3
         2          2 ccc,rr,125          3
         2          3                     1

6 rows selected

3 step: calc border values

SQL> with
  2  t1 as (
  3    select 1 n, 'c,b,a' c from dual
  4    union all
  5    select 2 n, 'vvv,ccc,rr' c from dual
  6    union all
  7    select 3 n, null c from dual),
  8  t2 as (
  9    select 1 n, 'a,b,c' c from dual
 10    union all
 11    select 2 n, 'ccc,rr,125' c from dual
 12    union all
 13    select 3 n, null c from dual),
 14  t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2),
 15  t as (select b, n, c, nvl(length(regexp_replace(c, '[^,]+')) + 1, 1) cnt from t12),
 16  tbrd as (select b, n, c, cnt, sum(cnt) over (order by b,n) brd from t)
 17  select * from tbrd
 18  /

         B          N C                 CNT        BRD
---------- ---------- ---------- ---------- ----------
         1          1 c,b,a               3          3
         1          2 vvv,ccc,rr          3          6
         1          3                     1          7
         2          1 a,b,c               3         10
         2          2 ccc,rr,125          3         13
         2          3                     1         14

6 rows selected

4 step: generate new query for rows

SQL> with
  2  t1 as (
  3    select 1 n, 'c,b,a' c from dual
  4    union all
  5    select 2 n, 'vvv,ccc,rr' c from dual
  6    union all
  7    select 3 n, null c from dual),
  8  t2 as (
  9    select 1 n, 'a,b,c' c from dual
 10    union all
 11    select 2 n, 'ccc,rr,125' c from dual
 12    union all
 13    select 3 n, null c from dual),
 14  t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2),
 15  t as (select b, n, c, nvl(length(regexp_replace(c, '[^,]+')) + 1, 1) cnt from t12),
 16  tbrd as (select b, n, c, cnt, sum(cnt) over (order by b,n) brd from t),
 17  allrec as (select level lv from dual connect by level <= (select sum(cnt) from t))
 18  select * from allrec
 19  /

        LV
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14

14 rows selected

5 step: main query

SQL> with
  2  t1 as (
  3    select 1 n, 'c,b,a' c from dual
  4    union all
  5    select 2 n, 'vvv,ccc,rr' c from dual
  6    union all
  7    select 3 n, null c from dual),
  8  t2 as (
  9    select 1 n, 'a,b,c' c from dual
 10    union all
 11    select 2 n, 'ccc,rr,125' c from dual
 12    union all
 13    select 3 n, null c from dual),
 14  t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2),
 15  t as (select b, n, c, nvl(length(regexp_replace(c, '[^,]+')) + 1, 1) cnt from t12),
 16  tbrd as (select b, n, c, cnt, sum(cnt) over (order by b,n) brd from t),
 17  allrec as (select level lv from dual connect by level <= (select sum(cnt) from t)),
 18  step5 as (select r.b, r.n, r.c, regexp_substr(c, '[^,]+', 1, r.brd - a.lv + 1) s from tbrd r, allrec a
 19  where r.brd - a.lv between 0 and r.cnt - 1)
 20  select * from step5
 21  /

         B          N C          S
---------- ---------- ---------- ----------
         1          1 c,b,a      a
         1          1 c,b,a      b
         1          1 c,b,a      c
         1          2 vvv,ccc,rr rr
         1          2 vvv,ccc,rr ccc
         1          2 vvv,ccc,rr vvv
         1          3            
         2          1 a,b,c      c
         2          1 a,b,c      b
         2          1 a,b,c      a
         2          2 ccc,rr,125 125
         2          2 ccc,rr,125 rr
         2          2 ccc,rr,125 ccc
         2          3            

14 rows selected

6 step: sorting values

SQL> with
  2  t1 as (
  3    select 1 n, 'c,b,a' c from dual
  4    union all
  5    select 2 n, 'vvv,ccc,rr' c from dual
  6    union all
  7    select 3 n, null c from dual),
  8  t2 as (
  9    select 1 n, 'a,b,c' c from dual
 10    union all
 11    select 2 n, 'ccc,rr,125' c from dual
 12    union all
 13    select 3 n, null c from dual),
 14  t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2),
 15  t as (select b, n, c, nvl(length(regexp_replace(c, '[^,]+')) + 1, 1) cnt from t12),
 16  tbrd as (select b, n, c, cnt, sum(cnt) over (order by b,n) brd from t),
 17  allrec as (select level lv from dual connect by level <= (select sum(cnt) from t)),
 18  step5 as (select r.b, r.n, r.c, regexp_substr(c, '[^,]+', 1, r.brd - a.lv + 1) s from tbrd r, allrec a
 19  where r.brd - a.lv between 0 and r.cnt - 1),
 20  step6 as (
 21  select b, n, listagg('[' || s || ']', ',') within group (order by s) sorted
 22  from step5
 23  group by b, n)
 24  select * from step6
 25  /

         B          N SORTED
---------- ---------- --------------------------------------------------------------------------------
         1          1 [a],[b],[c]
         1          2 [ccc],[rr],[vvv]
         1          3 []
         2          1 [a],[b],[c]
         2          2 [125],[ccc],[rr]
         2          3 []

6 rows selected

7 step: result

SQL> with
  2  t1 as (
  3    select 1 n, 'c,b,a' c from dual
  4    union all
  5    select 2 n, 'vvv,ccc,rr' c from dual
  6    union all
  7    select 3 n, null c from dual),
  8  t2 as (
  9    select 1 n, 'a,b,c' c from dual
 10    union all
 11    select 2 n, 'ccc,rr,125' c from dual
 12    union all
 13    select 3 n, null c from dual),
 14  t12 as (select 1 b, n, c from t1 union all select 2 b, n, c from t2),
 15  t as (select b, n, c, nvl(length(regexp_replace(c, '[^,]+')) + 1, 1) cnt from t12),
 16  tbrd as (select b, n, c, cnt, sum(cnt) over (order by b,n) brd from t),
 17  allrec as (select level lv from dual connect by level <= (select sum(cnt) from t)),
 18  step5 as (select r.b, r.n, r.c, regexp_substr(c, '[^,]+', 1, r.brd - a.lv + 1) s from tbrd r, allrec a
 19  where r.brd - a.lv between 0 and r.cnt - 1),
 20  step6 as (
 21  select b, n, listagg('[' || s || ']', ',') within group (order by s) sorted
 22  from step5
 23  group by b, n)
 24  select t.*, decode(
 25    (select 1 from step6 z
 26     where z.b = 2 and z.sorted = t.sorted and rownum < 2), 1, 'true', 'false') bool
 27  from step6 t where b = 1
 28  /

         B          N SORTED                                                                           BOOL
---------- ---------- -------------------------------------------------------------------------------- -----
         1          1 [a],[b],[c]                                                                      true
         1          2 [ccc],[rr],[vvv]                                                                 false
         1          3 []                                                                               true

Of course, it is much better to use functions, but, I just wanted to find a way using only SQL.

(2017-06-05: Added function)

Function can be:

create or replace function comparesepval(vArg1 in varchar2, vArg2 in varchar2) return number result_cache deterministic
as
  type tpStore is table of number index by varchar2(32);

  tStore1 tpStore;
  tStore2 tpStore;
  nCnt number;
  vVal varchar2(32);
begin
  if (vArg1 is null and vArg2 is null) or (vArg1 = vArg2) then
    return 1;
  end if;

  if vArg1 is null then
    return 0;
  end if;

  nCnt := nvl(length(regexp_replace(vArg1, '[^,]+')), 0) + 1;

  if nCnt = 1 or nCnt <> nvl(length(regexp_replace(vArg2, '[^,]+')), 0) + 1 then -- vArg1 <> vArg2
    return 0;
  end if;

  -- parse string
  for i in 1..nCnt
  loop
    declare

      procedure prcAgr(vArg in varchar2, tStore in out tpStore)
      as
        vVal varchar2(32) := nvl(regexp_substr(vArg, '(.*?)(,|$)', 1, i, null, 1), ','); -- ',' - for null
      begin
        tStore(vVal) := case when tStore.exists(vVal) then tStore(vVal) + 1 else 1 end;
      end;

    begin
      prcAgr(vArg1, tStore1);
      prcAgr(vArg2, tStore2);
    end;
  end loop;

  if tStore1.count <> tStore2.count then
    return 0;
  end if;

  vVal := tStore1.first;
  loop
    exit when vVal is null;
    if not tStore2.exists(vVal) then
      return 0;
    end if;
    if tStore2(vVal) <> tStore1(vVal) then
      return 0;
    end if;
    vVal := tStore1.next(vVal);
  end loop;

  return 1;
end;
/

Test:

SQL> with tbl as (
  2    select null c1, null c2 from dual union all
  3    select 'a' c1, 'a' c2 from dual union all
  4    select 'a' c1, 'b' c2 from dual union all
  5    select 'a,a' c1, 'a,b' c2 from dual union all
  6    select 'a,a' c1, 'a,b,a' c2 from dual union all
  7    select 'a,a,b' c1, 'a,b,a' c2 from dual union all
  8    select 'a,,b' c1, ',b,a' c2 from dual union all
  9    select 'a,' c1, 'd,' c2 from dual
 10    )
 11  select comparesepval(c1, c2) c, c1, c2 from tbl;

         C C1    C2
---------- ----- -----
         1       
         1 a     a
         0 a     b
         0 a,a   a,b
         0 a,a   a,b,a
         1 a,a,b a,b,a
         1 a,,b  ,b,a
         0 a,    d,

8 rows selected

Don't use a regex of the form `'[^,]+'` to parse delimited strings. It will fail if there is a NULL element. See here for more info: https://stackoverflow.com/a/31464699/2543416 — Gary_W, May 26 '17 at 12:41
It was to me too! I found an error in a production report that was reporting incorrect data (we have a vendor system out of our control that has columns containing lists of comma-separated values) due to this. Now I'm on a mission to warn people about it. The bad part is, it is the common regex found in reply to people searching for how to parse delimited lists. Join me in my mission! — Gary_W, May 26 '17 at 15:21
thanks a lot, can you share how to do this inside a function , if it match it will return 0 else 1. thanks in advance — Arch, Jun 02 '17 at 14:44

Compare 2 comma-separated columns in two different oracle tables

3 Answers3