RE: Oracle 10g SQL - Parsing Delimited String

Very nice, Stephen!

If the target string might include blanks, your code will extract the portion after the last blank.  In that case, this variation might work better:
           SUBSTR(      RTRIM(sample ,',')
                 ,INSTR(RTRIM(sample ,','),',',-1)
                  + 1)                 AS extracted

Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
314-694-2592

From: [email protected] [mailto:[email protected]] On Behalf Of Stephen Miller
Sent: Wednesday, January 11, 2012 12:03 PM
To: [email protected]; Vikram Vaidyanathan
Subject: Re: [toad] Oracle 10g SQL - Parsing Delimited String





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.







This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled
to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and
all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its
subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of "Viruses" or other "Malware".
Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying
this e-mail or any attachment.


The information contained in this email may be subject to the export control laws and regulations of the United States, potentially
including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of
Treasury, Office of Foreign Asset Controls (OFAC).  As a recipient of this information you are obligated to comply with all
applicable U.S. export laws and regulations.