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!

Converting string of text to rows

trishlApr 18 2018 — edited Apr 27 2018

HI,

I'm using oracle 12c and I am trying to convert a string of text into rows. THis is the string of test:

EN90:EN90-T:IS45:IS90:IS90-T:PE45:PE45-T:PE90:PE90-T:PESB:ST90:ST90-T

Id like the query to return a separate row for each of the above items separated by the ':' as shown below on the column 'PRODUCT_GROUP'.

EN90

EN90-T

IS45

IS90

I am trying to use the following query:

Select

c.ID,c.CUSTOMER_NUMBER,

regexp_substr(c.PRODUCT_GROUP,'[^:]+', 1,level) product_group

from

HAAS_PROD.CPR_PROD_PEL_HOP C

connect by regexp_substr(c.product_group, '[^:]+',1,level) is not null

This works fine with the exception that I get a bunch of extra rows duplicated with product group field.

I'm clearly missing something.

Any advice would be appreciated.

Thanks,

Trish

Locked Post

New comments cannot be posted to this locked post.

Locked on May 25 2018

Added on Apr 18 2018

8 comments

1,935 views