2500=2.5*10^3
如何将2500转换为2.5和3分为两个字段存储？
------解决方案--------------------
用一个比较笨的办法
with tmp as
(select rownum - 6 rn from dual connect by rownum < 15)
select num, num / power(10, rn), rn
from (select num,
(select max(rn) from tmp where num >= power(10, tmp.rn)) rn
from (select 2500 num
from dual
union all
select 37000
from dual
union all
select 100
from dual
union all
select 5
from dual
union all
select 0.01
from dual
union all
select 123 from dual))
------解决方案--------------------
可以通过计算获得的
length(trunc(2500))-1=3
round(2500/power(10,3),1)=2.5
------解决方案--------------------
--测试数据
with table_name(id,val)as(
select 1,1200 from dual
union all
select 2,3220 from dual
union all
select 3,8000 from dual
union all
select 4,0.34 from dual
)
--查询SQL
SELECT t.*, t.val / power(10, (length(trunc(t.val)) - 1)) col1, length(trunc(t.val)) - 1
FROM table_name t;