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

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.

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"
               );

Little modification to your query, assuming you can pick one char which will not be present in MY_STRING, e.g. pipe |

   with test as    
  (select '12 3,456,,,,abc,def' MY_STRING from dual)
  select trim('|' from regexp_substr(regexp_replace(MY_STRING,',,',',|,|'),'[^,]+',1,level)) SPLIT
  from test
  connect by level <= length (regexp_replace(MY_STRING, '[^,]+'))  + 1;

Output:

 SPLIT                 
-----------------------
12 3                    
456                     
(null)                        
(null)                         
(null)                         
abc                     
def  

No need of PL/SQL, you could do it in plain SQL. See Split comma delimited strings in a table in Oracle.

Using MODEL clause:

WITH sample_data AS (
SELECT '12 3,456,,,,,abc,def' str FROM dual
)
-- end of sample_data mimicking real table
,
model_param AS (
SELECT str AS orig_str ,
       ','
       || str
       || ','                                 AS mod_str ,
       1                                      AS start_pos ,
       Length(str)                           AS end_pos ,
       (LENGTH(str) -
       LENGTH(REPLACE(str, ','))) + 1        AS element_count ,
       0                                      AS element_no ,
       ROWNUM                                 AS rn
       FROM   sample_data )
       SELECT trim(Substr(mod_str, start_pos, end_pos-start_pos)) str
       FROM (
             SELECT *
             FROM   model_param
             MODEL PARTITION BY ( rn, orig_str, mod_str)
             DIMENSION BY (element_no)
             MEASURES (start_pos, end_pos, element_count)
             RULES ITERATE (2000)
             UNTIL (ITERATION_NUMBER+1 = element_count[0])
           ( start_pos[ITERATION_NUMBER+1] =
                     instr(cv(mod_str), ',', 1, cv(element_no)) + 1,
             end_pos[ITERATION_NUMBER+1] =
                     instr(cv(mod_str), ',', 1, cv(element_no) + 1) )
           )
        WHERE    element_no != 0
   ORDER BY      mod_str ,
                 element_no
   /

Output

STR
----------------------
12 3
456




abc
def

8 rows selected.

If you want to do it in PL/SQL, then you could use a pipelined table function:

SQL> CREATE OR REPLACE TYPE test_type
  2  AS
  3    TABLE OF VARCHAR2(100)
  4  /

Type created.

SQL> CREATE OR REPLACE FUNCTION comma_to_table(
  2      p_list IN VARCHAR2)
  3    RETURN test_type PIPELINED
  4  AS
  5    l_string LONG := p_list || ',';
  6    l_comma_index PLS_INTEGER;
  7    l_index PLS_INTEGER := 1;
  8  BEGIN
  9    LOOP
 10      l_comma_index := INSTR(l_string, ',', l_index);
 11      EXIT
 12    WHEN l_comma_index = 0;
 13      PIPE ROW ( TRIM(SUBSTR(l_string, l_index, l_comma_index - l_index)));
 14      l_index := l_comma_index                                + 1;
 15    END LOOP;
 16  RETURN;
 17  END comma_to_table;
 18  /

Function created.

Let's see the output:

SQL> SELECT *
  2  FROM TABLE(comma_to_table('12 3,456,,,,,abc,def'))
  3  /

COLUMN_VALUE
------------------------------------------------------------------------------
12 3
456




abc
def

8 rows selected.

SQL>