RE: Oracle 10g SQL - Parsing Delimited String

Just as an added note - and this probably goes without saying - you probably want to do a check to see if the value you are searching for exists first, if it does then do the task otherwise set some default value.

A substr starting at a position of 0 will give you the entire string.

Roger S.

From: Simoneau, Roger
Sent: Tuesday, January 10, 2012 2:50 PM
To: '[email protected]'
Subject: RE: [toad] Oracle 10g SQL - Parsing Delimited String

Substr combined with instr would do the job.

Roger S.

From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Vikram Vaidyanathan
Sent: Tuesday, January 10, 2012 2:25 PM
To: [email protected]<mailto:[email protected]>
Subject: [toad] Oracle 10g SQL - Parsing Delimited String


I need to write a generic SQL query to parse a comma delimited string from right to left and return the first non-null value.

Example:
1.  ,,,Database,,10g,,Oracle,,
2.  ,,10g,,,Oracle,,,,,,
3.  ,,,Data,,,Menu,,,Oracle,,,

For all the above examples, the output should be "Oracle"

Appreciate the help.

Thanks,
Vikram


________________________________
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.