需求:
#将数字填充到对应金额单中
select substr(b.payMoney,length(b.payMoney),1) 分,
substr(b.payMoney,length(b.payMoney)-1,1) 角,
case when length(b.payMoney)-3 <=0 then ''
else substr(b.payMoney,length(b.payMoney)-3,1)
end 圆,
case when length(b.payMoney)-4 <=0 then ''
else substr(b.payMoney,length(b.payMoney)-4,1)
end 拾,
case when length(b.payMoney)-5 <=0 then ''
else substr(b.payMoney,length(b.payMoney)-5,1)
end 百,
case when length(b.payMoney)-6 <=0 then ''
else substr(b.payMoney,length(b.payMoney)-6,1)
end 千,
case when length(b.payMoney)-7 <=0 then ''
else substr(b.payMoney,length(b.payMoney)-7,1)
end 万,
case when length(b.payMoney)-8 <=0 then ''
else substr(b.payMoney,length(b.payMoney)-8,1)
end 十万,
case when length(b.payMoney)-9 <=0 then ''
else substr(b.payMoney,length(b.payMoney)-9,1)
end 百万,
case when length(b.payMoney)-10 <=0 then ''
e end 亿
from dual;
lse substr(b.payMoney,length(b.payMoney)-10,1)
end 千万,
case when length(b.payMoney)-11 <=0 then ''
else substr(b.payMoney,length(b.payMoney)-11,1)
单据的样式如下:
左边是根据右边的数字进行变换为对应的金额大写,在上面的sql中我是用的是length(b.payMoney)获取的数字长度,其中b.payMoney就是数据库中查询出来的一个数字,
这个sql最致命的问题就是如果最终查询出来的数字是一个整数,即使我是用round/ceil/floor等保留小数的方式,最终得到的长度都不是想要的数据,默认将小数点后面的00去掉了
解决方案:
将其转换成to_char(b.payMoney,'999999999.99');这样写前面的九个9表示小数点前面可以最多九位,小数点后的两个9表示的是保留两位,使用这个查询出来的数据length()这个函数最终就没有问题