Re: Help with Regular Expressions

Ashish 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

Two ways ... the fast way, believe it or not, is instring/substring.

If none of the strings are more than 30 characters you can use dbms_utility.comma_to_table.

But given the example of your data, above, you aren't not going to do it successfully with either as you first need to deal with your double-quoted comma-delimited last name.

This is really rotten data. I'd suggest moving to vertical-bar delimiting.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org