Split comma separated values to columns in Oracle

Question

I have values being returned with 255 comma separated values. Is there an easy way to split those into columns without having 255 substr?

ROW  | VAL
----------- 
1    | 1.25, 3.87, 2, ... 
2    | 5, 4, 3.3, ....

to

ROW | VAL | VAL | VAL ...
--------------------- 
1   |1.25 |3.87 | 2 ...     
2   | 5   | 4   | 3.3 ...

why not use excel? and code it from there? – lookslikeanevo Jul 16 '15 at 21:00 — lookslikeanevo, Jul 16 '15 at 21:00
..because the automated process resides in the database. – alexherm Jan 24 '20 at 00:26 — alexherm, Jan 24 '20 at 00:26

score 27 · Answer 1 · edited Mar 24 '16 at 15:07

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 nulls It'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 '[^,]+'!

Thank you for this observation. It helped me a lot. I noticed one mistake in the explanation of the regex. This part is incorrect: * = 0 or more matches of the preceding character ? = Match 0 or 1 occurrences of the preceding pattern The regex wouldn't work if it were true. It should by something like: *? = Matches between zero and unlimited times, as few times as possible, expanding as needed (lazy) — bajermi2, Jan 30 '20 at 09:35
It is not working when delimiter is | (Pipeline) insted of , (Comma) — Jitendra Patel, Aug 24 '20 at 12:53
@JitendraPatel I suspect you are looking for `'(.*?)(\||$)'` You need to escape your literal pipe as it is a special character in the regex language, meaning OR. — Gary_W, Aug 24 '20 at 16:50

score 21 · Accepted Answer · answered Jul 16 '15 at 20:59

21

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.

answered Jul 16 '15 at 20:59

Gordon Linoff

1,242,037
58
646
786

3

This is not a correct answer, as the question explicitly states "without having 255 substr". Also, mind the Answer of Gary_W, it is a -very- valid concern. – Wouter Aug 14 '15 at 11:39
1

@Wouter . . . The point of the answer was to generate the values in a spreadsheet. – Gordon Linoff Aug 14 '15 at 12:50
Yes; clever. But still not an answer to the question at hand. – Wouter Aug 14 '15 at 14:09

score 3 · Answer 3 · edited May 23 '17 at 12:09

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 REPLACE
FUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)
  RETURN t_my_list
AS
  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;
/

score 2 · Answer 4 · answered Jan 13 '18 at 15:58

hierarchical query could be used. pivoting can be done with case and group by.

with value_t as
( 
  select row_t,row_number() OVER (partition by row_t order by rownum )rn,
  regexp_substr(val, '[^,]+', 1, LEVEL) val from Table1
CONNECT BY LEVEL <= regexp_count(val, '[^,]+') 
AND prior row_t = row_t 
AND prior sys_guid() is not null
  ) select row_t, max( case when rn = 1 THEN val end ) val_1,
  max( case when rn = 2 THEN val end ) val_2,
  max( case when rn = 3 THEN val end ) val_3
  from value_t
  group by row_t;

Split comma separated values to columns in Oracle

4 Answers4

Linked

Related