Split comma separated values to columns in Oracle

Beware! The regexp_substr expression of the format '[^,]+' will not return the expected value if there is a null element in the list and you want that item or one after it. Consider this example where the 4th element is NULL and I want the 5th element and thus expect the '5' to be returned:

SQL> select regexp_substr('1,2,3,,5,6', '[^,]+', 1, 5) from dual;R-6

Surprise! It returns the 5th NON-NULL element, not the actual 5th element! Incorrect data returned and you may not even catch it. Try this instead:

SQL> select regexp_substr('1,2,3,,5,6', '(.*?)(,|$)', 1, 5, NULL, 1) from dual;R-5

So, the above corrected REGEXP_SUBSTR says to look for the 5th occurrence of 0 or more comma-delimited characters followed by a comma or the end of the line (allows for the next separator, be it a comma or the end of the line) and when found return the 1st subgroup (the data NOT including the comma or end of the line).

The search match pattern '(.*?)(,|$)' explained:

(             = Start a group.             = match any character*             = 0 or more matches of the preceding character?             = Match 0 or 1 occurrences of the preceding pattern)             = End the 1st group(             = Start a new group (also used for logical OR),             = comma|             = OR$             = End of the line)             = End the 2nd group

EDIT: More info added and simplified the regex.

See this post for more info and a suggestion to encapsulate this in a function for easy reuse: REGEX to select nth value from a list, allowing for nullsIt's the post where I discovered the format '[^,]+' has the problem. Unfortunately it's the regex format you will most commonly see as the answer for questions regarding how to parse a list. I shudder to think of all the incorrect data being returned by '[^,]+'!

You can use regexp_substr():

select regexp_substr(val, '[^,]+', 1, 1) as val1,        regexp_substr(val, '[^,]+', 1, 2) as val2,        regexp_substr(val, '[^,]+', 1, 3) as val3,        . . .

I would suggest that you generate a column of 255 numbers in Excel (or another spreadsheet), and use the spreadsheet to generate the SQL code.

If you only have one row, and time to create your

• create your own built-in cto_table function to split a string on any separator, then you can use PIVOT + LISTAGG to do it like follows:

select * from (  select rownum r , collection.*      from TABLE(cto_table(',','1.25, 3.87, 2, 19,, 1, 9, ')) collection)PIVOT (   LISTAGG(column_value) within group (order by 1) as val   for r in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

FYI: here is how to create the cto_table function:

CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);CREATE OR REPLACEFUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)  RETURN t_my_listAS  l_string VARCHAR2(32767) := p_list || p_sep;  l_sep_index PLS_INTEGER;  l_index PLS_INTEGER := 1;  l_tab t_my_list     := t_my_list();BEGIN  LOOP    l_sep_index := INSTR(l_string, p_sep, l_index);    EXIT  WHEN l_sep_index = 0;    l_tab.EXTEND;    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));    l_index            := l_sep_index + 1;  END LOOP;  RETURN l_tab;END cto_table;/