在sql里怎么把一个数值型的字段中小数点后的数值取出来？

Question

比如t2.s_t的数值是99.67，类型是number(10,2)用下面的sql会报错，substring只能截varchar的，那number(10,2)的截取怎么写呢？
select substring(t2.s_t,3,2) from table t2

Answer 1

SELECT SUBSTR (TO_CHAR (t2.s_t),
               DECODE (INSTR (TO_CHAR (t2.s_t), '.'),
                       0, NULL,
                       INSTR (TO_CHAR (t2.s_t), '.') + 1
                      ),
               2
              )
  FROM table t2

Answer 2

select s_t - trunc(s_t) from t2

Answer 3

create table t2(s_t number(10,2))insert into t2 values(99.67)select s_t - trunc(s_t) from t2/*
MARKS_T-TRUNC(MARKS_T)
----------------------
                   .671 row selected.
*/

Answer 4

create table t2(s_t number(10,2))insert into t2 values(99.67)insert into t2 values(-99.67)select t2.* , s_t - trunc(s_t) from t2/*   MARKS_T MARKS_T-TRUNC(MARKS_T)
---------- ----------------------
     99.67                    .67
    -99.67                   -.672 rows selected.*/

Answer 5

select substr(to_char(t.s_t-trunc(t.s_t)),2)  from tab1 t

Answer 6

好注意，如负数不行加个ABS就行
select ABS(s_t) - ABS(trunc(s_t)) from t2

Answer 7

select substr(98.92,instr(98.92,'.')+1)from dual;