select case when right(to_char(t.salary/1000,'9,999,999.9999'),4)='0000' then left(to_char(t.salary/1000,'9,999,999.9999'),9) when right(to_char(t.salary/1000,'9,999,999.9999'),3)='000' then left(to_char(t.salary/1000,'9,999,999.9999'),11) when right(to_char(t.salary/1000,'9,999,999.9999'),2)='00' then left(to_char(t.salary/1000,'9,999,999.9999'),12) when right(to_char(t.salary/1000,'9,999,999.9999'),1)='0' then left(to_char(t.salary/1000,'9,999,999.9999'),13) else to_char(t.salary/1000,'9,999,999.9999') end as salary from tb
要是没有自己写的 left right函数 就这样select case when substr(to_char(t.salary/1000,'9,999,999.9999'),11,4)='0000' then substr(to_char(t.salary/1000,'9,999,999.9999'),1,9) when substr(to_char(t.salary/1000,'9,999,999.9999'),12,3)='000' then substr(to_char(t.salary/1000,'9,999,999.9999'),1,11) when substr(to_char(t.salary/1000,'9,999,999.9999'),13,2)='00' then substr(to_char(t.salary/1000,'9,999,999.9999'),1,12) when substr(to_char(t.salary/1000,'9,999,999.9999'),14,1)='0' then substr(to_char(t.salary/1000,'9,999,999.9999'),1,13) else to_char(t.salary/1000,'9,999,999.9999') end as salary from tb
直接 t.salary/10000 就可以了,不需要用to_char的
是啊,如果需要限定小数位数,用trunc函数,如trunc(2345678/1000,2)
select case when t.salary/10000 <1 then '0'||to_char( t.salary/10000 ) else ''||to_char( t.salary/10000 ) end aa from TABLE_NAME 看看这个是你要得结果吗?
看看高人的手段。
还要转格式干嘛
直接t.salary/1000
或者最多保留4位小数round(t.salary/1000)
这种格式就表示无论t.salary/1000的到的是几位小数,都会帮你转换成4位小数。
你的需求不需要使用to_char函数
select
case when right(to_char(t.salary/1000,'9,999,999.9999'),4)='0000' then left(to_char(t.salary/1000,'9,999,999.9999'),9)
when right(to_char(t.salary/1000,'9,999,999.9999'),3)='000' then left(to_char(t.salary/1000,'9,999,999.9999'),11)
when right(to_char(t.salary/1000,'9,999,999.9999'),2)='00' then left(to_char(t.salary/1000,'9,999,999.9999'),12)
when right(to_char(t.salary/1000,'9,999,999.9999'),1)='0' then left(to_char(t.salary/1000,'9,999,999.9999'),13)
else to_char(t.salary/1000,'9,999,999.9999') end as salary
from tb
substr(to_char(t.salary/1000,'9,999,999.9999'),14,1)='0' then substr(to_char(t.salary/1000,'9,999,999.9999'),1,13)
else to_char(t.salary/1000,'9,999,999.9999') end as salary
from tb
是啊,如果需要限定小数位数,用trunc函数,如trunc(2345678/1000,2)
''||to_char( t.salary/10000 ) end aa from TABLE_NAME
看看这个是你要得结果吗?