pl sql query to compare 2 columns comma separated values and find differences

Question

COL 1 Values: QQQ,QQ,123,VVVV
COL 2 VALUES: WWWW,VVV,QQQ

Compare COL1 values vs COL 2 values: For e.g.

1) If a value exist in COL1 but NOT in COL2 then display that COL1 value under COL VALUE ADDED

Output Expected: COL VALUE ADDED = QQ,123,VVVV

2) If a value exist in COL2 but NOT in COL1 then display that COL2 values under COL VALUE Removed.

Output Expected: COL VALUE REMOVED = WWWW,VVV

3) If the set of values are same within COL1 and COL2 then display it as NULL

Can these be handled and compared as expected dynamically via PLSQL command? I expect to have dynamic comma separated values that needs to be compared between Current and Before set of values.

score 0 · Answer 1 · answered Nov 12 '19 at 19:46

Here's one option:

SQL> with
  2  test (col1, col2) as
  3    (select 'QQQ,QQ,123,VVVV', 'WWWW,VVV,QQQ' from dual
  4    ),
  5  t1 (col) as
  6    (select regexp_substr(col1, '[^,]+', 1, level)
  7     from test
  8     connect by level <= regexp_count(col1, ',') + 1
  9    ),
 10  t2 (col) as
 11    (select regexp_substr(col2, '[^,]+', 1, level)
 12     from test
 13     connect by level <= regexp_count(col2, ',') + 1
 14    ),
 15  one_minus_two as
 16    (select col from t1
 17     minus
 18     select col from t2
 19    ),
 20  two_minus_one as
 21    (select col from t2
 22     minus
 23     select col from t1
 24    )
 25  select 'Col value added: ' ||
 26    listagg(col, ',') within group (order by null) as result
 27  from one_minus_two
 28  union all
 29  select 'Col value removed: ' ||
 30    listagg(col, ',') within group (order by null)
 31  from two_minus_one
 32  union all
 33  select 'NULL'
 34  from dual
 35  where (select listagg(col, ',') within group (order by col) from t1) =
 36        (select listagg(col, ',') within group (order by col) from t2);

RESULT
--------------------------------------------------------------------------------
Col value added: 123,QQ,VVVV
Col value removed: VVV,WWWW

SQL>

If both values are equal:

SQL> with
  2  test (col1, col2) as
  3    (select 'AAA,BBB,CCC', 'CCC,AAA,BBB' from dual
  4    ),
<SNIP>
 35  where (select listagg(col, ',') within group (order by col) from t1) =
 36        (select listagg(col, ',') within group (order by col) from t2);

RESULT
--------------------------------------------------------------------------------
Col value added:
Col value removed:
NULL

SQL>

score 0 · Answer 2 · answered Nov 12 '19 at 20:15

Here's one way to solve it:

WITH cteData AS (SELECT 'QQQ,QQ,123,VVVV' AS COL1,
                        'WWWW,VVV,QQQ' AS COL2
                   FROM DUAL),
     cteCol1 AS (SELECT REGEXP_SUBSTR(COL1, '[^,]+', 1, LEVEL) AS COL1
                   FROM cteData
                   CONNECT BY LEVEL < REGEXP_COUNT(COL1, ',')+2),
     cteCol2 AS (SELECT REGEXP_SUBSTR(COL2, '[^,]+', 1, LEVEL) AS COL2
                   FROM cteData
                   CONNECT BY LEVEL < REGEXP_COUNT(COL2, ',')+2),
     cteAdded AS (SELECT c1.COL1   -- COL1 not found in COL2
                    FROM cteCol1 c1
                    WHERE c1.COL1 NOT IN (SELECT COL2
                                            FROM cteCol2)),
     cteRemoved AS (SELECT c2.COL2  -- in COL2 but NOT in COL1
                      FROM cteCol2 c2
                      WHERE c2.COL2 NOT IN (SELECT COL1
                                              FROM cteCol1)),
     cteAdded_list AS (SELECT LISTAGG(a.COL1, ',') WITHIN GROUP (ORDER BY 1) AS ADDED
                        FROM cteAdded a),
     cteRemoved_list AS (SELECT LISTAGG(r.COL2, ',') WITHIN GROUP (ORDER BY 1) AS REMOVED
                           FROM cteRemoved r),
     cteResults AS (SELECT CASE
                             WHEN a.ADDED IS NULL THEN NULL
                             ELSE 'COL VALUE ADDED = ' || a.ADDED
                           END AS RESULT
                      FROM cteAdded_list a
                    UNION ALL
                    SELECT CASE
                             WHEN r.REMOVED IS NULL THEN NULL
                             ELSE 'COL VALUE REMOVED = ' || r.REMOVED
                           END AS RESULT
                      FROM cteRemoved_list r)
SELECT RESULT
  FROM cteResults
  WHERE RESULT IS NOT NULL

With the data you supplied this returns:

COL VALUE ADDED = 123,QQ,VVVV
COL VALUE REMOVED = VVV,WWWW

If you replace the strings in cteData with ones which are identical, e.g. set both to '123,456,789' then it returns an empty result set.

score 0 · Answer 3 · answered Nov 12 '19 at 21:41

Since you wanted a PL/SQL solution, you can create a function to split the string to an array and then use MULTISET operators:

Split Function:

From my previous answer

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN stringlist DETERMINISTIC
AS
  p_result       stringlist := stringlist();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

PL/SQL:

Then you can use it in a PL/SQL block:

DECLARE
  col1 VARCHAR2(4000) := 'QQQ,QQ,123,VVVV';
  col2 VARCHAR2(4000) := 'WWWW,VVV,QQQ';
  arr1 stringlist := SPLIT_STRING( col1 );
  arr2 stringlist := SPLIT_STRING( col2 );
  added   stringlist := arr1 MULTISET EXCEPT arr2;
  removed stringlist := arr2 MULTISET EXCEPT arr1;
BEGIN
  FOR i IN 1 .. added.COUNT LOOP
    DBMS_OUTPUT.PUT( added(i) || ',' );
  END LOOP;
  DBMS_OUTPUT.NEW_LINE();

  FOR i IN 1 .. removed.COUNT LOOP
    DBMS_OUTPUT.PUT( removed(i) || ',' );
  END LOOP;
  DBMS_OUTPUT.NEW_LINE();
END;
/

Which outputs:

dbms_output:
QQ,123,VVVV,
WWWW,VVV,

SQL:

If you want to implement it in SQL then with the test data:

CREATE TABLE test_data ( col1, col2 ) AS
SELECT 'QQQ,QQ,123,VVVV', 'WWWW,VVV,QQQ' FROM DUAL;

You can query it using:

SELECT ( SELECT LISTAGG( column_value, ',' ) WITHIN GROUP ( ORDER BY ROWNUM )
         FROM   TABLE( a.arr1 MULTISET EXCEPT a.arr2 ) ) AS added,
       ( SELECT LISTAGG( column_value, ',' ) WITHIN GROUP ( ORDER BY ROWNUM )
         FROM   TABLE( a.arr2 MULTISET EXCEPT a.arr1 ) ) AS removed
FROM   (
  SELECT SPLIT_STRING( col1 ) AS arr1,
         SPLIT_STRING( col2 ) AS arr2
  FROM   test_data
) a;

Which outputs:

ADDED       | REMOVED 
:---------- | :-------
QQ,123,VVVV | WWWW,VVV

db<>fiddle here

pl sql query to compare 2 columns comma separated values and find differences

3 Answers3