Forum Stats

  • 3,815,480 Users
  • 2,259,036 Discussions
  • 7,893,127 Comments

Discussions

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

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

Answers