PL/SQL List Parsing Utility
I have had need of a utility such as the one being provided in this entry on almost every major Oracle development project I've been involved in over the last ten years. The utility, a PL/SQL package called ListUtils, performs three key functions on a delimited string or list:
(1) It provides the means to retrieve any element at a given position in the list in a single request.
(2) It will count and return the number of elements in a list.
(3) It will compare two lists (or strings) for absolute equality.
Another key feature of this utility is it ability to reduce a list by removing all of the null entries before retrieving a desired element. Examples are given in the PL/SQL package spec below. Please feel free to use or improve this code:
/*=======================================================*/
/* Jason Bennett */
/* PL/SQL Package Containing Three Utilities for */
/* manipulating a list or string of delimited elements */
/* such as A,B,C,D or A B C D */
/* 11/15/2004 */
/*=======================================================*/
CREATE OR REPLACE PACKAGE ListUtils AUTHID DEFINER AS
/**
/* The getListElement Function returns the desired element in
/* a delimited list based upon the elements position in the list.
/*
/* Example:
/* ListUtils.getListElement('A,B,C,D,E',4,',')
/* would return D.
/*
/* This function can also reduce or clean a list of all null elements
/* using the p_clean_parse parameter.
/*
/* Example:
/* ListUtils.getListElement(',,,,,,A,,B,,,C,,,,,D,,,E',4,',','Y')
/* would still return D.
/*
/* Note: A delimiter can be any character or series of characters ','
/* and 'xyzsdd' are both acceptable delimiters.
**/
FUNCTION getListElement( p_string_in IN VARCHAR2,
p_desired_element IN NUMBER,
p_delimiter IN VARCHAR2 := ',',
p_clean_parse IN VARCHAR2 := 'N')
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(getListElement,WNDS,WNPS);
/**
/* The listElementCount function returns the number of elements
/* in a delimited list.
/*
/* Example:
/* ListUtils.listElementCount('A,B,C,D,E',',')
/* would return 5
/*
/* This function can also return a count of all none null elements
/* using the p_clean_count parameter.
/*
/* Example:
/* ListUtils.listElementCount(',,,,,,A,,B,,,C,,,,,D,,,E',',','Y')
/* would still return 5.
/*
**/
FUNCTION listElementCount(p_string_in IN VARCHAR2,
p_delimiter IN VARCHAR2 := ',',
p_clean_count IN VARCHAR2 := 'N' )
RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(listElementCount,WNDS,WNPS);
/**
/* The listComp Function compares two delimited lists to
/* determine if they are exact matches. White space and
/* case count.
/*
/* Example:
/* ListUtils.listComp('a,b,c','a,b,c')
/* returns TRUE
/*
/* ListUtils.listComp('a,b,c','a,b, c')
/* returns FALSE
**/
FUNCTION listComp(p_list_1 VARCHAR2,p_list_2 VARCHAR2) RETURN BOOLEAN;
PRAGMA RESTRICT_REFERENCES(listComp,WNDS,WNPS);
END;
/
sho err;
/**********************************************************************/
CREATE OR REPLACE PACKAGE BODY ListUtils AS
FUNCTION removeRepeatChars(p_string VARCHAR2 := NULL,
p_repeat_char VARCHAR2 := NULL)
RETURN VARCHAR2 IS
ret_val VARCHAR2(32000) := NULL;
BEGIN
IF INSTR(p_string,p_repeat_char||p_repeat_char) = 0 THEN
ret_val := p_string;
ELSE
ret_val := REPLACE(p_string,p_repeat_char||p_repeat_char,p_repeat_char);
ret_val := removeRepeatChars(ret_val,p_repeat_char);
END IF;
RETURN ret_val;
END;
/**********************************************************************/
FUNCTION countString(p_string VARCHAR2 := NULL,
p_target VARCHAR2 := NULL,
p_pos NUMBER :=0)
RETURN NUMBER IS
ret_val NUMBER(12) := 0;
BEGIN
IF INSTR(p_string,p_target,1,p_pos+1) < p_pos THEN
ret_val := 0;
ELSE
ret_val := countString(p_string,p_target,p_pos+1) + 1;
END IF;
RETURN ret_val;
END;
/**********************************************************************/
FUNCTION getListElement( p_string_in IN VARCHAR2,
p_desired_element IN NUMBER,
p_delimiter IN VARCHAR2 := ',',
p_clean_parse IN VARCHAR2 := 'N')
RETURN VARCHAR2
IS
v_edit_string VARCHAR2(32000);
v_delimiter_count NUMBER(4) := 0;
v_element_count NUMBER(4) := 0;
v_start_position NUMBER(4);
v_end_position NUMBER(4);
e_end_process EXCEPTION;
BEGIN
IF (INSTR(p_string_in,p_delimiter)=0) THEN
IF (p_desired_element != 1) THEN
RETURN NULL;
ELSE
RETURN p_string_in;
END IF;
END IF;
/* HANDLE NULL STRING */
IF p_string_in IS NULL THEN
RAISE e_end_process;
ELSE
v_edit_string := p_string_in;
END IF;
/* REMOVE EXTRA DELIMITERS */
IF UPPER(p_clean_parse) = 'Y' THEN
-- Remove Leading and trailing delimiters
v_edit_string := RTRIM(LTRIM(v_edit_string,p_delimiter),p_delimiter);
-- Reduce consecutive repeating delimiters to one delimiter between elements
v_edit_string := removeRepeatChars(v_edit_string,p_delimiter);
-- Get Count of delimiters
v_delimiter_count := countString(v_edit_string,p_delimiter);
-- There always be one more element in a list than there are delimiters.
v_element_count := v_delimiter_count+1;
/*****************/
/* SPECIAL CASES */
/*****************/
/* IF DELIMITER COUNT IS 0 THEN RETURN THE ENTIRE STRING. */
IF (v_delimiter_count = 0) AND (p_desired_element = 1) THEN
RAISE e_end_process;
END IF;
/* DISQUALIFY BOGUS REQUESTS FOR ELEMENTS */
IF (p_desired_element > v_element_count) OR (p_desired_element < 1) THEN
v_edit_string := NULL;
RAISE e_end_process;
END IF;
ELSE
-- There always be one more element in a list than there are delimiters.
v_element_count := listElementCount(v_edit_string,p_delimiter);
END IF; /* CLEAN PARSING */
/********************************/
/* ISOLATE THE DESIRED ELEMENT. */
/********************************/
/* FIND STARTING POSITION OF DESIRED ELEMENT */
IF (p_desired_element = 1) THEN
v_start_position := 1;
ELSE
v_start_position := (INSTR(v_edit_string,p_delimiter,1,p_desired_element-1)+length(p_delimiter));
END IF;
/* FIND ENDING POSITION OF DESIRED ELEMENT */
IF (p_desired_element = v_element_count) THEN
v_end_position := LENGTH(v_edit_string);
ELSE
v_end_position := (INSTR(v_edit_string,p_delimiter,v_start_position,1) - v_start_position);
END IF;
/* GET REQUESTED ELEMENT */
v_edit_string := SUBSTR(v_edit_string,v_start_position,v_end_position);
RETURN v_edit_string;
EXCEPTION
WHEN e_end_process THEN
RETURN v_edit_string;
END;
/*****************************************************************/
FUNCTION listElementCount(p_string_in IN VARCHAR2,
p_delimiter IN VARCHAR2 := ',',
p_clean_count IN VARCHAR2 := 'N') RETURN NUMBER
IS
e_end_process EXCEPTION;
v_count_string VARCHAR2(32000) := p_string_in;
v_element_count NUMBER(12) := 0;
BEGIN
/* HANDLE NULL STRING */
IF v_count_string IS NULL THEN
RAISE e_end_process;
END IF;
IF p_clean_count = 'Y' THEN
-- Remove Leading and trailing delimiters
v_count_string := RTRIM(LTRIM(v_count_string,p_delimiter),p_delimiter);
-- Reduce consecutive repeating delimiters to one delimiter between elements
v_count_string := removeRepeatChars(v_count_string,p_delimiter);
-- Get Count of elements
END IF;
v_element_count := countString(v_count_string,p_delimiter) + 1;
RETURN v_element_count;
EXCEPTION
WHEN e_end_process THEN
RETURN 0;
END;
/**************************************************************/
FUNCTION listcomp(p_list_1 VARCHAR2,
p_list_2 VARCHAR2) RETURN BOOLEAN
IS
v_return BOOLEAN := TRUE;
BEGIN
Lists of unequal length are not equal.
IF LENGTH(p_list_1) != LENGTH(p_list_2) THEN
v_return := FALSE;
ELSE
-- Perform a character by character comparison.
FOR step IN 1 .. LENGTH(p_list_1) LOOP
IF SUBSTR(p_list_1,step,1) != SUBSTR(p_list_2,step,1) THEN
v_return := FALSE;
EXIT;
END IF;
END LOOP;
END IF;
RETURN v_return;
END;
END;
/
SHO ERR
