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

[charlesxu]

比如t2.marks_t的数值是99.67，类型是number(10,2)

用下面的sql会报错，substring只能截varchar的，那number(10,2)的截取怎么写呢？

select substring(t2.marks_t,3,2) from table t2

[007-x]

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

[dawugui]

select marks_t - trunc(marks_t) from t2

[007-x]

[Quote=引用 2 楼 dawugui 的回复:]
select marks_t - trunc(marks_t) from t2
[/Quote]
好主意,但是仅限正数,负数就不灵了

[dawugui]

create table t2(marks_t number(10,2))

insert into t2 values(99.67)

select marks_t - trunc(marks_t) from t2

/*
MARKS_T-TRUNC(MARKS_T)
----------------------
                   .67

1 row selected.
*/

[dawugui]

create table t2(marks_t number(10,2))

insert into t2 values(99.67)

insert into t2 values(-99.67)

select t2.* , marks_t - trunc(marks_t) from t2

/*

   MARKS_T MARKS_T-TRUNC(MARKS_T)
---------- ----------------------
     99.67                    .67
    -99.67                   -.67

2 rows selected.

*/

[programmerxiaocai]

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

[007-x]

[Quote=引用 5 楼 dawugui 的回复:]
SQL code

create table t2(marks_t number(10,2))

insert into t2 values(99.67)

insert into t2 values(-99.67)

select t2.* , marks_t - trunc(marks_t) from t2

/*

MARKS_T MARKS_T-TRUNC(M……
[/Quote]
顶一个

[yu15tian]

[Quote=引用 3 楼 tx2730 的回复:]

引用 2 楼 dawugui 的回复:
select marks_t - trunc(marks_t) from t2

好主意,但是仅限正数,负数就不灵了
[/Quote]

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

[huangtao_love]

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