Comparing lists in Oracle

While Oracle is incredibly powerful, there are some things it generally isn’t asked to do. One of those things is compare a list of values. You can compare recordsets, tables, strings, etc… But trying to compare two separate lists of values can be a challenge.  Other languages such has ColdFusion have quite a few LIST functions baked in, but there are times I don’t want to (or can’t) switch from Oracle to CF for processing.

Below is a package I wrote with two functions I have found I need from time to time. If you are needing them every day, you should probably re-evaluate your data model… but there are times you have to deal with the data you got.

First, declare a datatype you will need.

CREATE TYPE LIST_PACK_TYPE AS TABLE OF VARCHAR2(4000);
/

Compile the package

CREATE OR REPLACE PACKAGE LIST_PACK 
AS
  FUNCTION LIST_CONVERT_FUNC
   ( P_LIST IN VARCHAR2,
     P_DELIM IN VARCHAR2 DEFAULT ',' )
   RETURN LIST_PACK_TYPE
   PIPELINED;
 --
 FUNCTION LIST_SINGLE_MATCH_FUNC
 ( P_LIST1 IN VARCHAR2,
   P_LIST2 IN VARCHAR2,
   P_DELIM IN VARCHAR2 DEFAULT ','
 )
 RETURN NUMBER;
 --
END LIST_PACK;
/

CREATE OR REPLACE PACKAGE BODY LIST_PACK 
AS
 FUNCTION LIST_CONVERT_FUNC
 ( P_LIST IN VARCHAR2,
   P_DELIM IN VARCHAR2 DEFAULT ',' 
 )
 RETURN LIST_PACK_TYPE
 PIPELINED
 AS
   L_STRING LONG := P_LIST ||P_DELIM;
   L_DELIM_INDEX PLS_INTEGER;
   L_INDEX PLS_INTEGER := 1;
 BEGIN
 LOOP
   L_DELIM_INDEX := INSTR(L_STRING, P_DELIM, L_INDEX);
   EXIT WHEN L_DELIM_INDEX = 0;
   PIPE ROW (SUBSTR(L_STRING, L_INDEX, L_DELIM_INDEX - L_INDEX));
   L_INDEX := L_DELIM_INDEX + 1;
 END LOOP;
 RETURN;
 END LIST_CONVERT_FUNC;
 --
 FUNCTION LIST_SINGLE_MATCH_FUNC
 ( P_LIST1 IN VARCHAR2,
   P_LIST2 IN VARCHAR2,
   P_DELIM IN VARCHAR2 DEFAULT ','
 )
 RETURN NUMBER
 IS
   v_MATCHES NUMBER;
   GET_OUT EXCEPTION;
 BEGIN
   IF TRIM(P_LIST1) IS NULL AND TRIM(P_LIST2) IS NULL THEN
     v_MATCHES := 0;
     RAISE GET_OUT;
   END IF;
   SELECT COUNT(*) INTO v_MATCHES 
   FROM ( SELECT COLUMN_VALUE 
          FROM TABLE(LIST_CONVERT_FUNC(P_LIST1,P_DELIM))
          INTERSECT 
          SELECT COLUMN_VALUE 
          FROM TABLE(LIST_CONVERT_FUNC(P_LIST2,P_DELIM))
        );
   IF v_MATCHES >= 1 THEN
     RETURN 1;
   ELSE 
     RETURN 0;
   END IF;
 EXCEPTION
   WHEN GET_OUT THEN
     RETURN v_MATCHES;
 END LIST_SINGLE_MATCH_FUNC;
 --
END LIST_PACK;
/

Examples:

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','3,4,5') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','3,4,5')
-------------------------------------------------
 1

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','4,5,6') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','4,5,6')
-------------------------------------------------
 0

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','3,4,5',',') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','3,4,5',',')
-----------------------------------------------------
 1

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','4,5,6',',') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1,2,3','4,5,6',',')
-----------------------------------------------------
 0

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','3|4|5','|') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','3|4|5','|')
-----------------------------------------------------
 1

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','4|5|6','|') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','4|5|6','|')
-----------------------------------------------------
 0

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','4|5|6',',') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC('1|2|3','4|5|6',',')
-----------------------------------------------------
 0

sql>SELECT LIST_PACK.LIST_SINGLE_MATCH_FUNC(NULL,NULL,',') FROM DUAL;

LIST_PACK.LIST_SINGLE_MATCH_FUNC(NULL,NULL,',')
-----------------------------------------------
 0

Advertisements