Pick number after comma

Question

Working with plsql I am dividing two numbers, and I would like to receive only what comes after the comma. Ex: 7,89111. I need only number 89111.

Answer 1

Expensive,

The only way I could find was by converting the number to String and making a substring with it.

I put the value with the point instead of comma to do the test, but you can put your field in the two values that works well.

select substr(to_char(7.89111), instr(to_char(7.89111), ',') + 1) from dual;

Upshot:

SUBST
-----
89111

Answer 2

I would do so, no need to convert the value to string, then when you run a calculation with the same, you will have to convert again to number...

SELECT SUBSTR(7.89111, INSTR(7.89111, '.', 1, 1) + 1, 5) value FROM dual;

Answer 3

If you want to take the decimal part, do this:

SELECT 7.89111 - TRUNC(7.89111) from dual;

Result:0,89111

If you only want to get the numbers after the comma:

select REPLACE((7.89111 - trunc(7.89111)),',','') from dual;

Result: 89111