Oracle: Breaking Comma Separated String Using DBMS_UTILITY.COMMA_TO_TABLE

Posted by
February 17, 2009
  

There are times one has to break a comma separated string and extract actual values from it. Possible reasons for this requirement could be when a procedure takes an unknown number of strings as an input (although arrays/collections can be used instead, but right now we’ll stick to a single string) or while reading csv file etc.

Oracle provides a DBMS_UTILITY procedure called COMMA_TO_TABLE. This procedure assumes a comma delimiter, if the delimiter is something else, a REPLACE would need to be done beforehand.

Following code snippet displays the use of COMMA_TO_TABLE:

Sample Code:

 
DECLARE
  lv_Str_List       VARCHAR2(1000) := 'Alpha, Beta, Gama, Delta';
  lb_cnt             BINARY_INTEGER;
  la_Tab_Str       DBMS_UTILITY.UNCL_ARRAY;
BEGIN
  -- parse the string into comma separated table
  DBMS_UTILITY.COMMA_TO_TABLE(lv_Str_List, lb_cnt, la_Tab_Str);
  FOR i IN 1 .. la_Tab_Str.COUNT LOOP
    -- display substring
    DBMS_OUTPUT.PUT_LINE(TRIM(la_Tab_Str(i)));
  END LOOP;
END;
/
 

Output:
Alpha
Beta
Gama
Delta

Although quite useful, this procedure has it’s own restrictions as it was written primarily for use within replication internally by Oracle, and parse IDENTIFIERS rather than strings. Refer “*IMPORTANT CAVEAT*” in http://www.oratechinfo.co.uk/delimited_lists_to_collections.html#dbms_utility. Due to this restriction, names starting with numbers or including special characters are not supported.
e.g. if the above snippet had :

 
lv_Str_List       VARCHAR2(1000) := 'Alpha, Beta, Gama, Delta, 123number';
 

or

 
lv_Str_List       VARCHAR2(1000) := 'Alpha, Beta, Gama, Delta, specialchar#@!@&**';
 

The following error would be returned:
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at “SYS.DBMS_UTILITY”, line 125
ORA-06512: at “SYS.DBMS_UTILITY”, line 160
ORA-06512: at “SYS.DBMS_UTILITY”, line 202
ORA-06512: at line 8

However, a workaround can be used for the procedure to support this. If the not-supported substrings are included in double quotes, they can be parsed by this procedure. The easier way to implement this is to put all the substrings in the comma separated strings within double quotes. Following code snippet shows how:

Sample Code:

 
DECLARE
  lv_Str_List          VARCHAR2(1000) := 'Alpha, Beta, Gama, Delta, 123number';
  lb_cnt                BINARY_INTEGER;
  la_Tab_Str          DBMS_UTILITY.UNCL_ARRAY;
  lv_Str_List_Quote VARCHAR2(3000);
BEGIN
  -- put all the strings in double quotes to avoid special character problems 
  -- while parsing with comma_to_table procedure
  lv_Str_List_Quote := '"' || REPLACE( lv_Str_List, ',', '","' ) || '"';
 
  -- parse the string into comma separated table
  DBMS_UTILITY.COMMA_TO_TABLE(lv_Str_List_Quote, lb_cnt, la_Tab_Str);
 
  FOR i IN 1 .. la_Tab_Str.COUNT LOOP
    -- remove double quotes added earlier and trim to fetch the actual string
    DBMS_OUTPUT.PUT_LINE(TRIM(REPLACE( la_Tab_Str(i), '"', '' )));
  END LOOP;
END;
/
 

Output:
Alpha
Beta
Gama
Delta
123number

In the above snippet, the comma separated string is started and appended with ” (double quote) and all , (comma) are replaced by “,” (double quote comma double quote) so that each substring starts and ends with a double quote. Then, the strings are extracted into a table using COMMA_TO_TABLE. These strings still are within double quotes, so the for loop fetches each string and the double quote is replaced with nothing. Also, the final string is trimmed to clean any extra leading or trailing spaces.

Note: There is a TABLE_TO_COMMA which does the reverse.

Most Commented Posts

converter, database, oracle, string

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments
Comment by Abhishek on September 19, 2011 @ 1:15 pm

By experience, some of us know that if we pass comma separated values as a string variable Eg:- ’101,103′ to the IN clause, it does not give the desired output as illustrated below -

CREATE TABLE SUBJECT_TABLE(SUBJECT_ID VARCHAR2(10), SUBJECT_NAME VARCHAR2(25));

INSERT INTO SUBJECT_TABLE (SUBJECT_ID, SUBJECT_NAME) VALUES (’101′, ‘MATHEMATICS’);
INSERT INTO SUBJECT_TABLE (SUBJECT_ID, SUBJECT_NAME) VALUES (’102′, ‘ENGLISH’);
INSERT INTO SUBJECT_TABLE (SUBJECT_ID, SUBJECT_NAME) VALUES (’103′, ‘SCIENCE’);
INSERT INTO SUBJECT_TABLE (SUBJECT_ID, SUBJECT_NAME) VALUES (’104′, ‘HISTORY’);
COMMIT;

An anonymous block as below will not Print any output –

DECLARE
P_SUBJECT_ID VARCHAR2(100) := NULL;
CURSOR CUR_SUBNAME IS
SELECT SUBJECT_NAME
FROM SUBJECT_TABLE
WHERE SUBJECT_ID IN (P_SUBJECT_ID);
BEGIN
P_SUBJECT_ID := ’101,103′;
FOR CUR_SUBNAME_REC IN CUR_SUBNAME LOOP
DBMS_OUTPUT.PUT_LINE(CUR_SUBNAME_REC.SUBJECT_NAME);
END LOOP;
END;
/

However, when we use “Regular expression & Connect by” clause to convert the Parameter string to column values and use in Cursor, we’ll get the desired output as explained below -

DECLARE
P_SUBJECT_ID VARCHAR2(100) := NULL;
CURSOR CUR_SUBNAME IS
SELECT SUBJECT_NAME
FROM SUBJECT_TABLE
WHERE SUBJECT_ID IN
((SELECT REGEXP_SUBSTR(P_SUBJECT_ID, ‘[^,]+’, 1, LEVEL)
FROM DUAL
CONNECT BY REGEXP_SUBSTR(P_SUBJECT_ID, ‘[^,]+’, 1, LEVEL) IS NOT NULL));
BEGIN
P_SUBJECT_ID := ’101,103′;
FOR CUR_SUBNAME_REC IN CUR_SUBNAME LOOP
DBMS_OUTPUT.PUT_LINE(CUR_SUBNAME_REC.SUBJECT_NAME);
END LOOP;
END;
/

This may be useful when parameter list is prepared by adding comma separated value(s) within different blocks and finally passed to a cursor or SQL statement for use within IN clause.

Leave a comment

(required)

(required)