OPTIONALLY ENCLOSED BY. can we use in sql query? or what alternate solution for this?

User_M3HXT

Hi,

oracle 12.2.9

db version 18c

we are getting .csv(comma separated) file form external source. and need to split_string into 1 TABLE type Array field and then need to insert into interface table. but as i can see in .csv(comma separated) file amount field having "," between amount i.e "71,007,498.00"

i have this value "71,007,498.00",0.00,0.00,"71,007,498.00",

so while splitting this value, it should be like

 lv_data_tbl := split_string('"71,007,498.00",0.00,0.00,"71,007,498.00",' , ',');

expacted output

lv_data_tbl(1)=71,007,498.00

lv_data_tbl(2)=0.00

lv_data_tbl(3)=0.00

lv_data_tbl(4)=71,007,498.00

but getting this output:-

lv_data_tbl(1)=71,007,498.00

lv_data_tbl(2)=007

lv_data_tbl(3)=498.00

lv_data_tbl(4)=0.00

lv_data_tbl(5)=0.00

lv_data_tbl(6)=71

lv_data_tbl(7)=007

lv_data_tbl(8)=498.00

EdStevens

Also asked at https://stackoverflow.com/questions/70334372/optionally-enclosed-by-can-we-use-in-sql-query-or-what-alternate-solution-for