Recent Posts
Categories
Advertisements
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.
CREATE TYPE LIST_PACK_TYPE AS TABLE OF VARCHAR2(4000); /
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; /
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
Recent Comments