Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Commas enclosed by quotes in a sting

621995Jun 7 2011 — edited Jun 7 2011
Hello
I am using utl_file.fopen to read records from a csv file ( I know about External tables but am not permitted to use them!) Within the csv record I have strings that look like this

sample of records variable name is : l_text_rec2

'77432,,Wellside Surgery,PE20 5SU,,,,,2,24/02/2011,,S00112043,,"2,912.90",582.5,"3,495.00",0,02/03/2011'

77459,,"Drs Caranllici, Franklin, & Laws",B17 4LA,,,,,2,24/02/2011,,S0002057,,"2,000.00",400,"2,400.00",0,21/03/2011'

I have tried a regular expression to remove the quotes and commas within quotes

l_text_rec2:=REGEXP_REPLACE ( l_text_rec2, '("[^"]*),([^"]*")', '\1;\2');
l_text_rec2 :=replace(l_text_rec2,'"','');
l_text_rec2 :=replace(l_text_rec2,';','');

However this will only remove a single comma e.g. ' yellow, "gr,een1",blue' to yellow,green1,blue' great but a

Problem occurs when multiple commas exist ' yellow, "gr,een,1",blue' goes to 'yellow,green,1, blue' when I want 'yellow,green1,blue'

Anyone can help me address removing commas from delimited quotes within a sting?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 5 2011
Added on Jun 7 2011
8 comments
1,492 views