Separate comma separated string into columns oracle sql

Question

I have a column in a table that will have values separated by commas. It appears that the maximum number of entries is 5, but I am not certain of that. I need a way to select the individual values at and put each entry in its own column. It is a select statement only, not building a new table.

create table commasplit (agentname varchar2(40), categories varchar2(100));
insert into commasplit (select 'Alice', 'cat01, cat02, cat03' from dual);
insert into commasplit (select 'Bob', 'cat04, cat05' from dual);
insert into commasplit (select 'Carl', 'cat02, cat06, cat01, cat11' from dual);

So I would like to select it so that I get these results:

AGENT   CATA   CATB   CATC   CATD
Alice   cat01  cat02  cat03  null
Bob     cat04  cat05  null   null
Carl    cat02  cat06  cat01  cat11

@astentx, yes it does, thank you. Not sure how I missed that when I was searching. — Nicko, Aug 05 '21 at 11:51

score 2 · Accepted Answer · answered Aug 04 '21 at 22:54

You can use REGEXP_SUBSTR, but you must specify the number of columns.

SELECT agentname
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 1)    AS CATA
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 2)    AS CATB  
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 3)    AS CATC 
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 4)    AS CATD
FROM commasplit;

demo in db<>fiddle

Separate comma separated string into columns oracle sql

1 Answers1