navid sedigh navid sedigh -4 years ago 161
SQL Question

Split string in Oracle with regexp_substr in order

I have a string in Oracle database, my string is: 'bbb;aaa;qqq;ccc'

I used regexp for split my string:

select distinct trim(regexp_substr('bbb;aaa;qqq;ccc','[^;]+', 1,level) ) as q
from dual
connect by regexp_substr('bbb;aaa;qqq;ccc', '[^;]+', 1, level) is not null ;


I want to split it in order, I expected the below output always:

bbb

aaa

qqq

ccc

because order of the subString are very important for me. but the result of this query is not in order:

qqq

aaa

bbb

ccc

Answer Source

You don't need a DISTINCT to get your result; besides, to get the result in a given order, all you need is an ORDER BY clause:

select trim(regexp_substr('bbb;aaa;qqq;ccc','[^;]+', 1,level) ) as q 
from dual
connect by regexp_substr('bbb;aaa;qqq;ccc', '[^;]+', 1, level) is not null
order by level
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download