How to generate data in a column from a comma-separated string?

Question

I want to generate a column from a comma-separated string, e.g. "a,b,c,d"

Expected column should be as shown below. Request for a oracle sql solution.Thank you!

col
----
a
b
c
d

Does this answer your question? [How to convert comma separated values to rows in oracle?](https://stackoverflow.com/questions/38371989/how-to-convert-comma-separated-values-to-rows-in-oracle) There are many more examples. This has been asked and answered every day since the mid 90s. I wrote [this](https://www.williamrobertson.net/documents/comma-separated.html) in 2004. — William Robertson, Jul 31 '20 at 20:28

score 2 · Answer 1 · answered Jul 29 '20 at 13:20

You can use REGULAR EXPRESSIONS , INSTR in CONNECT BY clause.

The following example might help you-

WITH data AS (
    SELECT
        'a,b,c,d' str
    FROM
        dual
)
SELECT
    regexp_substr(str, '[^,]+', 1, level) col
FROM
    data
CONNECT BY
    level <= regexp_count(str, ',') + 1;

or let me make it simpler as

    SELECT
    regexp_substr('a,b,c,d', '[^,]+', 1, level) col
FROM
    dual
CONNECT BY
    level <= regexp_count('a,b,c,d', ',') + 1;

for more clarification check this

Good idea to use `REGEXP_COUNT`. We have done this many times but haven't used `REGEXP_COUNT`. We used to have the `CONNECT BY` condition as `CONNECT BY regexp_substr(str, '[^,]+', 1, level) IS NOT NULL` — Arijit Kanrar, Jul 29 '20 at 16:43

score 1 · Answer 2 · answered Jul 29 '20 at 14:47

The easiest way is to use xmltable and "tokenize" function:

For example your table is T and column name is STR:

select * 
from t, 
     xmltable('tokenize(., ",")' 
              passing t.str 
              columns 
                n for ordinality,
                substring varchar2(10) path '.'
              ) x

First parameter of "tokenize" function is your string and second one is your delimiter. The dot (.) is your string from passing clause here:

tokenize(., ",")

Full example:

with t(str) as (select 'a,b,c,d' str from dual)
select * 
from t, 
     xmltable('tokenize(., ",")' 
              passing t.str 
              columns 
                n for ordinality,
                substring varchar2(10) path '.'
              ) x;

Results:

STR              N SUBSTRING
------- ---------- ----------
a,b,c,d          1 a
a,b,c,d          2 b
a,b,c,d          3 c
a,b,c,d          4 d

George Joseph · Answer 3 · 2020-07-29T13:18:29.607

The following should work

create table t(col1 varchar2(10))

insert 
  into t
select 'a,b,c,d' 
  from dual 

with data
  as (select ','||col1||',' as col1
       from t
      )
 select substr(col1
              ,instr(col1,',',1,level)+1
              ,instr(col1,',',1,level+1)
               -
               instr(col1,',',1,level)
               -1
               )
        ,level 
   from data
connect by level<=length(col1)-length(replace(col1,','))-1
order by 2

+------------+-------+
| COL_VALUES | LEVEL |
+------------+-------+
| a          |     1 |
| b          |     2 |
| c          |     3 |
| d          |     4 |
+------------+-------+

db fiddle link https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=0686f9015e0ceb56c12a50349d6b5efd

score 0 · Answer 4 · answered Jul 29 '20 at 12:46

0

Easy

SQL> with t as (
  2  select 'a' as id from dual union all
  3  select 'b' as id from dual union all
  4  select 'c' as id from dual )
  5* select listagg(id,',') within group (order by id) from t
SQL> /

LISTAGG(ID,',')WITHINGROUP(ORDERBYID)
--------------------------------------------------------------------------------
a,b,c

answered Jul 29 '20 at 12:46

Roberto Hernandez

8,231
3
14
43

This make the seperated value aggregated into a string, but what I want is a one column table from a string "a,b,c,d" – ScorpJ Jul 29 '20 at 12:50

score 0 · Answer 5 · answered Jul 29 '20 at 12:52

0

In SQL Server:

Select value from STRING_SPLIT('a,b,c,d', ',');

answered Jul 29 '20 at 12:52

David Wu

1

score 0 · Answer 6 · answered Jul 29 '20 at 13:26

select substr(src, start_pos, decode(end_pos, 0, length(src)+1, end_pos)-start_pos) val
from (
    select src,
        level lvl,
        decode(level, 1, 1, instr(src, ',', 1, level-1)+1) start_pos,
        instr(src, ',', 1, level) end_pos
    from (select 'a,bb,ccc,dddd' src from dual)
    connect by instr(src, ',', 1, level-1) > 0
)

How to generate data in a column from a comma-separated string?

6 Answers6