Re: Oracle 10g SQL - Parsing Delimited String

SELECT sample
          ,SUBSTR(      TRIM(REPLACE(sample ,',' ,' '))
                 ,INSTR(TRIM(REPLACE(sample ,',' ,' '))
                 ,' ' ,-1) + 1)                 AS extracted
      FROM
   (SELECT ',,Test This,,'                  AS sample FROM DUAL
   );

So, if you have embedded spaces, Stephen's solution will give the wrong
result.
Mike


On Wed, Jan 11, 2012 at 10:02 AM, Stephen Miller <[email protected]>wrote:

> **
>
>
>  The following should work in all current versions of ORACLE:
>
> *    SELECT sample
>           ,SUBSTR(      TRIM(REPLACE(sample ,',' ,' '))
>                  ,INSTR(TRIM(REPLACE(sample ,',' ,' '))
>                  ,' ' ,-1) + 1)                 AS extracted
>       FROM
>    (SELECT ',,Oracle10,,'                  AS sample FROM DUAL UNION ALL
>     SELECT ',,,Database,,10g,,Oracle10,,'  AS sample FROM DUAL UNION ALL
>     SELECT ',,10g,,,Oracle20,,,,,,'        AS sample FROM DUAL UNION ALL
>     SELECT ',,,Data,,,Menu,,,OracleAA,,,,' AS sample FROM DUAL
>    )*
>
> *  SAMPLE                        EXTRACTED
>   ----------------------------- ---------
> 1 ,,Oracle10,,                  Oracle10
> 2 ,,,Database,,10g,,Oracle10,,  Oracle10
> 3 ,,10g,,,Oracle20,,,,,,        Oracle20
> 4 ,,,Data,,,Menu,,,OracleAA,,,, OracleAA*
>
> This replaces all of the commas with spaces, trims the result, and
> substrings out the last word in the resulting string - works even if there
> is only one string in the result.
>
>
>
>  Stephen Miller - email: miller_stephen at usa.net
>
>
>
>
> ------ Original Message ------
> *Received: *05:49 PM EST, 01/10/2012
> *From: *Vikram Vaidyanathan <[email protected]>
> *To: *"[email protected]" <[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.
>
>
>
>
>  
>