Help with regexp_substr() in Oracle SQL

Look at this sample:
regexp_substr('123;test;456;Error: Test','[^[;]+') returns 123
regexp_substr('123;test;456;Error: Test','[^[;]+',1,2) returns Test
regexp_substr('123;test;456;Error: Test','[^[;]+',2,3) returns 456 etc.

Open in new window

but what if e.g. piece 2 (delimiter ;) is blank? Then piece 3 becomes piece 2:
regexp_substr('123;;456;Error: Test','[^[;]+') returns 123
regexp_substr('123;;456;Error: Test','[^[;]+',1,2) returns 456
regexp_substr('123;;456;Error: Test','[^[;]+',2,3) returns Error: Test

Open in new window

How do I change the regexp string so the right piece is returned regardless of whether it is empty or not?

Iver Erling ArvaSenior consultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Iver Erling ArvaSenior consultantAuthor Commented:
Unbelievably there doesn't seem to be an Oracle function to split delimited strings. Why this is I cannot tell, but it is quite unbelievable after all these years.

Something like
select piece(textstring, delimiter, piece{_from} {,piece_to}) from dual

Open in new window

and you shouldn't have to know if it is in the beginning, middle or end, just the piece number and delimiter.

Nearly all text data files I have seen uses the CSV format or similar, and it is an extremely common way to distribute data, so why there isn't such a function seem both strange and stupid. Also an approach like the one suggested above would be very easy to use and very concistent for the user.
slightwv (䄆 Netminder) Commented:
I'm thinking this can be cleaned up a little but here is what I came up with to start:
with mydata as (
select '123;;456;Error: Test' mystring from dual
select level, ltrim(regexp_substr(mystring,'.[^;]*',1,level),';')
from mydata
connect by level <= regexp_count(mystring,';')+1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Just wanted to clarify slightwv's answer. You need to use * (which is zero or more occurrences) instead of +
Iver Erling ArvaSenior consultantAuthor Commented:
Cool! Thanks! Brilliant! I have looked so many places to find a SQL code example of a truly consistent way picking pieces from a record that doesn't require special handling of the first or last piece etc. This is the first time I've seen it.

All the best!
IVer in Oslo
slightwv (䄆 Netminder) Commented:
Glad to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.