17,377
社区成员
发帖
与我相关
我的任务
分享
SQL> select purmf_price from tbtest;
PURMF_PRICE
--------------------------
2,0.00,;1,17.70,;0,18.66,;
SQL>
SQL> select substr('#'||purmf_price,
2 instr('#'||purmf_price,'#',1,rn)+1,
3 instr('#'||purmf_price,',',1,rn)-instr('#'||purmf_price,'#',1,rn)-1) as nums,
4 substr('#'||purmf_price,
5 instr('#'||purmf_price,',',1,rn)+1,
6 instr('#'||purmf_price,'#',1,rn+1)-instr('#'||purmf_price,',',1,rn)-1) as prices
7 from (
8 select replace(purmf_price,',;','#') as purmf_price from tbtest
9 )t1,
10 (
11 select rownum as rn
12 from all_objects a
13 where rownum <= 5
14 )tt
15 where instr('#'||purmf_price,'#',1,rn) * instr('#'||purmf_price,',',1,rn) > 0;
NUMS PRICES
------------------------ ------------------------
2 0.00
1 17.70
0 18.66