Oracle- Split string comma delimited (string contains spaces and consecutive commas)

Question

I can't find a solution about how to split a comma-delimited string in ORACLE. Searched a lot, nothing works for my case

Code

DECLARE
  TYPE T_ARRAY_OF_VARCHAR IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
  MY_ARRAY T_ARRAY_OF_VARCHAR;
  MY_STRING VARCHAR2(2000) := '12 3,456,,abc,def';
BEGIN
 FOR CURRENT_ROW IN (
    with test as    
      (select MY_STRING from dual)
      select regexp_substr(MY_STRING, '[^,]+', 1, rownum) SPLIT
      from test
      connect by level <= length (regexp_replace(MY_STRING, '[^,]+'))  + 1)

  LOOP
   DBMS_OUTPUT.PUT_LINE('>' || CURRENT_ROW.SPLIT || '<');
   --DBMS_OUTPUT.PUT_LINE(CURRENT_ROW.SPLIT);
    MY_ARRAY(MY_ARRAY.COUNT) := CURRENT_ROW.SPLIT;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Array Size:' || MY_ARRAY.COUNT);
END;

/

The output is:

>12 3<
>456<
>abc<
>def<
><
Array Size:5

The empty value is out of order!!!!

Answer 1

Try this for the parsing the list part. It handles NULLS:

SQL> select regexp_substr('12 3,456,,abc,def', '(.*?)(,|$)', 1, level, null, 1) SPLIT, level
    from dual
    connect by level <= regexp_count('12 3,456,,abc,def',',') + 1
    ORDER BY level;

SPLIT                  LEVEL
----------------- ----------
12 3                       1
456                        2
                           3
abc                        4
def                        5

SQL>

Unfortunately when you search for regex's for parsing lists, you will always find this form which does NOT handle nulls and should be avoided: '[^,]+'. See here for more info: Split comma separated values to columns in Oracle.

Answer 2

Try xmltable and flwor expresion. The following example is not secure and throw error if you put string without comma. But is simpler to understand.

select xmlcast(column_value as varchar2(2000))  value_list 
  from xmltable('for $val in ora:tokenize($strList,",") 
                  return $val' 
                 passing '12 3,456,,abc,def' as "strList"
                ); 

And secured version.

    select xmlcast(column_value as varchar2(2000)) value_list 
      from xmltable('for $val at $index in ora:tokenize(concat(",",$strList),",") 
       where $index > 1 
       return $val' passing '12 3,456,,abc,def' as "strList"
               );