Re: Oracle 10g SQL - Parsing Delimited String

Thanks Nate. Excellent solution. That works.

 
From: "SCHROEDER, NATHAN E (AG/1000)" <[email protected]>
To: "[email protected]" <[email protected]> 
Sent: Tuesday, January 10, 2012 3:27 PM
Subject: RE: [toad] Oracle 10g SQL - Parsing Delimited String


  
Oops – there’s a bug.  That formula won’t work if there’s only one non-comma character in the whole string.  But this one will work:
 
regexp_substr(regexp_substr(','||i.i||',','([^,].*[^,]|[^,])'),'[^,]+$')
 
Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
314-694-2592
 
From:SCHROEDER, NATHAN E [AG/1000] 
Sent: Tuesday, January 10, 2012 5:20 PM
To: '[email protected]'
Subject: RE: [toad] Oracle 10g SQL - Parsing Delimited String
 
This should work in Oracle 10:
 
regexp_substr(regexp_substr(','||i.i||',','[^,].*[^,]'),'[^,]+$')result
 
The inner regexp_substr says: I want the (longest possible, which is implied) string that starts with a non-comma and ends with a non-comma.  This strips the leading and trailing commas if any.
 
The outer regexp_substr takes the result of that and says: I want the (longest possible) string that contains no commas and ends at the end of the string.
 
Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
314-694-2592
 
From:SCHROEDER, NATHAN E [AG/1000] 
Sent: Tuesday, January 10, 2012 5:13 PM
To: [email protected]
Subject: RE: [toad] Oracle 10g SQL - Parsing Delimited String
 
Perfect application of regular expressions.
 
withi as(
select',,,Database,,10g,,Oracle10,,'i fromdual unionall
select',,10g,,,Oracle20,,,,,,'i fromdual unionall
select',,,Data,,,Menu,,,OracleAA,,,,'i fromdual )
selectregexp_substr(','||i.i||',','.*,([^,]+),*',1,1,null,1)result
     ,i.i
fromi;
 
RESULT         I
Oracle10        ,,,Database,,10g,,Oracle10,,
Oracle20        ,,10g,,,Oracle20,,,,,,
OracleAA       ,,,Data,,,Menu,,,OracleAA,,,,
 
-- oops – I used the “subexpr” parameter of regexp_substr, which is new with Oracle 11g.  But maybe you can get (more) creative and find a way that works in 10g?
 
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 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
 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.