求助，截取一NUMBER（10,2）类型整数部分前7位，小数点以及小数点后两位

Question

试过不满位，也没问题。。就是觉得太麻烦了select substr(to_char(12345678.12),
              1,
              (select case
                        when instr(to_char(12345678.12), '.') - 1 <= 7 then
                         instr(to_char(12345678.12), '.') - 1
                        else
                         7
                      end
                 from dual)) ||
       substr(to_char(12345678.12), instr(to_char(12345678.12), '.'), 3)
  from dual;

Answer 1

select substr(to_char(trunc(1234567.89)),1,7) from dual;

Answer 2

SELECT SUBSTR(TO_CHAR(TRUNC(12345678.12)), 0, 7)|| TO_CHAR( MOD(12345678.12,1)) FROM DUAL;

Answer 3

忘了整数了
SELECT SUBSTR(TO_CHAR(TRUNC(&tp)), 0, 7)||DECODE(MOD(&tp,1), 0, '', TO_CHAR(MOD(&tp,1))) FROM DUAL;

Answer 4

SELECT substr(regexp_substr(to_char(123456.12),'\w+',1,1),1,7),regexp_substr(to_char(123456781021.12),'\W'),
regexp_substr(to_char(123456.12),'\w+',1,2)
from dual;