How to convert comma delimited values to records?

Question

In Oracle SQL, I have a set of strings like...

Select 'AX', 'BC' from dual;

I need these strings as separate records as below.

Required Output:

Column1
---------------
AX

BC

Those aren't comma delimited values, just hard coded column values. — Joakim Danielson, Apr 15 '19 at 06:47

score 2 · Accepted Answer · edited Apr 15 '19 at 07:05

2

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;

edited Apr 15 '19 at 07:05

Barbaros Özhan

59,113
10
31
55

answered Apr 15 '19 at 06:46

Arun Palanisamy

5,281
6
28
53

score 2 · Answer 2 · answered Apr 15 '19 at 06:46

2

One way would be like this

with t as (Select 'AX' col1, 'BC' col2 from dual)
select col1 from t
union 
select col2 from t

answered Apr 15 '19 at 06:46

hol

8,255
5
33
59

Ahamed · Answer 3 · 2019-04-15T07:21:05.787

2

Please check the below code. Hope this helps to achieve your results.

with table1 as
 (Select 'AX,BC' as data1 from dual)

SELECT REGEXP_SUBSTR(data1, '[^,]+', 1, LEVEL) TXT
  FROM table1
CONNECT BY level <= length(regexp_replace(data1, '[^,]+')) + 1;

edited Apr 15 '19 at 07:21

answered Apr 15 '19 at 06:49

Ahamed

312
1
8

How to convert comma delimited values to records?

3 Answers3