SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

regexp_substr split a string into rows by a single character delimiter but a multi character condit

f.seuberthAug 3 2016 — edited Aug 4 2016

Hello,

i need to split a string into rows in SQL by a single character delimiter but a multi character condition.

Here the Test case (can have more the 3 entries):

with act as

(select upper(replace('10298 trace name context forever, level 32, 28401 TRACE NAME CONTEXT FOREVER, LEVEL 1, 4711 foo baar, 1257 one more event',' ','')) value from dual)

select value from act;

10298TRACENAMECONTEXTFOREVER,LEVEL32,28401TRACENAMECONTEXTFOREVER,LEVEL1,4711FOOBAAR,1257ONEMOREEVENT

Splitting by ',' ( select regexp_substr(value ,'[^,]+', 1, level) from act connect by regexp_substr(value , '[^,]+', 1, level) is not null; ) would be easy but the technical result is wrong.

The split condition sould be "by the comma which is followed by a digit".

The result i needis:

10298TRACENAMECONTEXTFOREVER,LEVEL32

28401TRACENAMECONTEXTFOREVER,LEVEL1
4711FOOBAAR

1257ONEMOREEVENT

Can anyone please help?

Many thanks

Frank

regards

Frank

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 1 2016

Added on Aug 3 2016

7 comments

2,976 views