Convert Delimited String to Rows in Oracle

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.

Converting a delimited string (or column) to rows in Oracle using a pre-defined system function

The function I was trying to find was SYS.DBMS_DEBUG_VC2COLL.

Technically speaking it does not convert a delimited string to column, but it converts a list of comma separated values to rows. I realized that after I found an old post.

Sample code and results:

with test as  (
    select column_value AS c1  
      from table( SYS.DBMS_DEBUG_VC2COLL( 'a','b','c' ) )  
   )  
 select * from test;

Result:

c1   
__  
a    
b    
c    

Split String into rows Oracle SQL

This should do the trick:

SELECT DISTINCT ID, regexp_substr("Strings", '[^ ]+', 1, LEVEL)
FROM T
CONNECT BY regexp_substr("Strings", '[^ ]+', 1, LEVEL) IS NOT NULL
ORDER BY ID;

Notice how I used regexp_substr in the connect by clause too. This is to deal with the case of multiple spaces.

---

If you have a predictable upper bound on the number of items per line, it might worth comparing the performances of the recursive query above with a simple CROSS JOIN:

WITH N as (SELECT LEVEL POS FROM DUAL CONNECT BY LEVEL < 10)
--                                                       ^^
--                                                 up to 10 substrings
SELECT ID, regexp_substr("Strings", '[^ ]+', 1, POS)
FROM T CROSS JOIN N
WHERE regexp_substr("Strings", '[^ ]+', 1, POS) IS NOT NULL
ORDER BY ID;

See http://sqlfiddle.com/#!4/444e3/1 for a live demo

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 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; 

How to split a string in order with a comma-separated string and (possible) empty fields between commas

with test as (select 'value1;value2;;;value5;value6;' line from dual)
select nvl(regexp_substr (line, '([^;]*);', 1, rownum, null, 1), 'none') as split
from test
connect by level <= regexp_count (line, ';');

SPLIT                         
------------------------------
value1
value2
none
none
value5
value6

6 rows selected. 

---

Related Topics