Re: Help with Regular Expressions

Am not going to argue against the points made by all of you... But there are certain things which cannot be changed at this point in time...

I got some help with regular expressions

SQL>with t as (select '5/10/2007,36352737,"Ronald M. Reed, Jr.", 211001,7204,-75.00' str from dual)
  2 --
  3 select trim('"' from regexp_substr(str,'".*?"|[^,]+',1,level)) sub_str from t
  4 connect by level<=length(regexp_replace(str,'".*?"|[^,]*'))+1

But am unable to figure out how to handle cases where there is no data in the comma delimited string...

Can anyone help me build the correct expression?

Ashish
On Jul 3, 9:55 pm, romeo.olym..._at_gmail.com wrote:
> On Jul 4, 9:22 am, Ashish <srivastava.ash..._at_gmail.com> wrote:
>
>
>
> > Hi,
>
> > Hi,
>
> > We have a comma separated text file, that we need to read in PL/SQL.
> > Does anyone know of a quick and clean method to split the line into
> > its components?
>
> > For example, for a line : 5/10/2007,,"Ronald M. Reed, Jr.",
> > 211001,7204,-75.00
> > I should fetch 6 values:
> > 5/10/2007
> > <NULL>
> > Ronald M. Reed, Jr
> > 211001
> > 7204
> > -75.00
>
> > I can use a combination of Substr and instr, but the problem comes
> > when we have a ',' in the content of some field. How do we ignore
> > that?
>
> > Any help would be greatly appreciated.
>
> > Thanks
> > Ashish
>
> Or if you can use SQL*Loader or external tables to read the file first
> (might be better if you're dealing with large files), then this would
> be standard stuff:
>
> ...
> fields delimited by ','
> optionally enclosed by '"'
> (
> col1 date "mm/dd/yyyy",
> col2 char,
> col3_name char,
> col4 decimal external,
> col5 decimal external,
> col6 decimal external
> )
Received on Tue Jul 03 2007 - 22:14:57 CDT