How to Convert Comma Separated Values to Rows in Oracle

Oracle - how to convert comma seperated lists to rows?

You can use multiset and hierarchy query to split your comaa separated data into rows using following:

select
  t.staff_id, 
  trim(regexp_substr(t.schedule, '[^,]+', 1, levels.column_value))  as schedule
from 
  Your_table t,
  table(cast(multiset(select level from dual 
                       connect by level <= regexp_count(t.schedule, ',') + 1) 
             as sys.OdciNumberList)) levels
order by staff_id, schedule;

How can I combine multiple rows into a comma-delimited list in Oracle?

Here is a simple way without stragg or creating a function.

create table countries ( country_name varchar2 (100));

insert into countries values ('Albania');

insert into countries values ('Andorra');

insert into countries values ('Antigua');


SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
      FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
                   COUNT (*) OVER () cnt
              FROM countries)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

CSV                                                                             
--------------------------
Albania,Andorra,Antigua                                                         

1 row selected.

As others have mentioned, if you are on 11g R2 or greater, you can now use listagg which is much simpler.

select listagg(country_name,', ') within group(order by country_name) csv
  from countries;

CSV                                                                             
--------------------------
Albania, Andorra, Antigua

1 row selected.

Convert delimited string to rows in oracle

Riffing off this method for splitting a string while allowing for nulls:

select regexp_substr('A~^B~^C~D^E','(.*?)(~\^|$)', 1, level, null, 1) from dual
connect by level < regexp_count('A~^B~^C~D^E','(.*?)(~\^|$)');

REGEXP_SUBS
-----------
A
B
C~D^E

As in the linked answer it looks for any characters, non-greedily, followed by the combination of ~^ (with the caret escaped, so ~\^) or end-of-line. The regexp_substr() calls also uses the optional arguments to specify subexpr - so it only gets the first grouping (.*?) and not the delimiter itself, which is in the second grouping.

If you want a specific element then that's even closer to the linked post:

select regexp_substr('A~^B~^C~D^E','(.*?)(~\^|$)', 1, 3, null, 1) from dual;

REGEX
-----
C~D^E

Or as you're doing this in a procedure, use the connect-by query to populate a collection, and then pick out the element you need, if you'll be looking at more than one.

How to convert comma delimited values to records?

In Oracle, you can try this

with mydata as 
     (select q'[AX,BC]' mycol from dual)
       select regexp_substr(mycol, '[^,]+', 1, level) result from mydata
       connect by level <= length(regexp_replace(mycol, '[^,]+')) + 1; 

---

Related Topics