Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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.orgReceived on Tue Jul 03 2007 - 20:43:20 CDT
![]() |
![]() |