RE: Oracle 10g SQL - Parsing Delimited String

Roger's principle still applies. The following variation of his SQL shows the last character of the last non null string:-

SELECT INSTR (',,,Database,,10g,,Oracle,,',
              SUBSTR (REPLACE (',,,Database,,10g,,Oracle,,', ',', NULL), -1),
              -1)
  FROM DUAL;

He is correct. You can use what he has shown, with SUBSTR and INSTR to do what you want.

Regards,

Mike McAllister 
Principal Systems Engineer
Decypher
DSN: 		487-3751
Commercial: 	(210) 652-3751
Cell: 		(512) 423-7447
Email: 		[email protected]


-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Vikram Vaidyanathan
Sent: Tuesday, January 10, 2012 4:44 PM
To: [email protected]
Subject: Re: [toad] Oracle 10g SQL - Parsing Delimited String

  

I am sorry if I wasn't very clear and precise.
 
The keyword in the example I gave i.e. Oracle can change and can be any string.
 
For example the column will contain values
 
1.  ,,,Database,,10g,,Oracle10,,
2.  ,,10g,,,Oracle20,,,,,,
3.  ,,,Data,,,Menu,,,OracleAA,,,,
 
The output column should contain:
Oracle10
Oracle20
OracleAA
 
From: "Simoneau, Roger" <[email protected]>
To: "'[email protected]'" <[email protected]> 
Sent: Tuesday, January 10, 2012 2:08 PM
Subject: RE: [toad] Oracle 10g SQL - Parsing Delimited String


  
>> Appreciate if you cna elaborate.
 
Instr <- oracle function that identifies the position of a value within another value, using a negative starting position indicates a reverse “look up”
 
So…
 
In the string “hello world, hello world, hello world”, entering:
 
Select instr(txt, ‘hello world’) from dual
 
Will respond with: 1 <- the position of the first occurrence
 
Select instr(txt, ‘hello world’, -1) from dual
 
Will respond with: 27 <- the third occurrence
 
Using substr from the position returned you can structure exactly what you want extracted.
 
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.