Split string csv to rows in Oracle's SQL

Question

Need to split a string into rows. Empty values should be separate rows in the same order.

value: ',,hello,,,world,,'

1 -> 
2 -> 
3 -> hello
4 -> 
5 -> 
6 -> world
7 -> 
8 ->

I tried following query as suggested in various threads.

   WITH
   qstr AS (select ',,,,1,2,3,4,,' str from dual)
   SELECT level ||'->'||
   REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) value FROM qstr
   CONNECT BY LEVEL <= REGEXP_COUNT(str, ',') + 1

It moves all empty values down in the list of rows and nonempty to the top. I would like to maintain the order as well.

1->hello
2->world
3->
4->
5->
6->
7->
8->

For now I am using a hack to insert empty string before each empty value delimiter which is working. I was wondering if there any efficient way of doing it.

   WITH
   qstr AS (select replace(',,,,1,2,3,4,,',',',' ,') str from dual)
   SELECT level ||'->'||
   REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) value FROM qstr
   CONNECT BY LEVEL <= REGEXP_COUNT(str, ',') + 1

Result:

1->
2->
3->hello
4->
5->
6->world
7->
8->

this would better at code review, you have already a working code, you don' need the duplicate, but for a better code we should open this — nbk, Mar 07 '23 at 20:24
From the linked duplicate, you can do it using simple string functions (which are much faster than regular expressions) [fiddle](https://dbfiddle.uk/x5rNwjTm) — MT0, Mar 07 '23 at 22:29

score 1 · Accepted Answer · answered Mar 07 '23 at 20:27

To cut a long story short: regular expression should be somewhat fixed - because of empty elements in the list.

Instead of [^,]+, use [^,]*,?:

SQL> with test (col) as
  2    (select ',,hello,,,world,,' from dual)
  3  --
  4  select lvl,
  5         replace(regexp_substr(col, '[^,]*,?', 1, lvl), ',') val
  6  from test cross apply (select level lvl
  7                         from dual
  8                         connect by level <= regexp_count(col, ',') + 1
  9                        )
 10  order by lvl;

         LVL VAL
------------ ----------
           1
           2
           3 hello
           4
           5
           6 world
           7
           8

8 rows selected.

SQL>

Split string csv to rows in Oracle's SQL

1 Answers1