Commas enclosed by quotes in a sting
621995Jun 7 2011 — edited Jun 7 2011Hello
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?