Re: Oracle 10g SQL - Parsing Delimited String

 
Thanks Roger for your wonderful inisght and leading me towards the correct solution. It had got me thinking in the right direction until Nate served it out to me in a golden platter.
 
Thanks all for the wonderful help. Much appreciate it.

From: "Simoneau, Roger" <[email protected]>
To: "'[email protected]'" <[email protected]> 
Sent: Tuesday, January 10, 2012 3:56 PM
Subject: RE: [toad] Oracle 10g SQL - Parsing Delimited String


  
>> I am sorry if I wasn't very clear and precise.
 
Ahh… I think I get you now.  Let’s use slightly different data in order to be absolutely clear:
 
1.  ,,,Database,,10g,,Oracle10,,
2.  ,,John,,,,Smith,,,
3.  ,Unicorn,,,,,,,Horns,
 
So, your data output is:
 
Oracle10
Smith
Horns
 
In other words:
a)  You don’t know what the last value will be is
 
I certainly did misunderstand your initial question.
 
Michael showed how it could still work with the instr and substr but like myself, he didn’t give you your solution on a silver platter.  He gave you a solution that can identify the final non-separation character in your string.
 
A clue to the rest of your solution is that if you know the start of your value and you know the end of your value, you can use substr to extract the value.
 
Identifying the first value – O, S or H in the example data above – isn’t so easy.  But it is very easy to identify the position of the final separator character before your final “word” now that you know the position of the last character in the “word”.
 
Of course, Nate possibly provided the most elegant solution.  But you’ll want to understand your regular expression syntax very well so you can avoid interesting bugs.
 
Roger S.
This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.