Avatar of pm-archtect
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Oracle 11g Split column value and then find matching rows to create single row


I really don't know how to do this:

I want to split a value such as 123-456-789-0 into it's constituent parts and then find all the matching rows in a table and subsequently create a single row from the results.  If you review the attached file, you will see example data for the starting point and the end result.

It is important to understand that when the values are found and joined together, the resulting column order is important (as the example data will show you)

Many thanks
Oracle Database

Avatar of undefined
Last Comment

8/22/2022 - Mon
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

This is perfect - exactly what I needed.  May I be cheeky and extend this (I don't mind asking another question separtely if you want to grab that one).

I need to join the resulting list back to a table where the UNITID would be the key.  The only way to do this would be to see if any of the values in the CONSISTID match.  So I would want to split the CONSISTID again into consituent parts e.g. 10-202-312-30 becomes


and ask, do any of these values match the UNITID record in the other table, if so, then join the records together.  There will multiple rows in the other table with the same UNITID

As I say, perhaps I should ask this as a separate question?
Sean Stuber

glad I could help,  yes,  adding new requirements should be a new question.

I'm not sure what you're asking in the addendum anyway,  be sure to include a complete example in the new question.

This was a perfect answer - thank you and very prompt.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck