Oracle plsql how to check if number has decimal points

Question

Just started working with oracle using toad ide. trying to format the numbers from a table in specific format. the numbers come in from a variable in the table and I want to display the whole numbers as whole numbers and display floats as floats. So far, I can use trim(TO_CHAR (width,'999.999')) to display all numbers with decimal points.

For example: 123.5 will be displayed as 123.500 and 100 will be displayed as 100.000.

What I want to do is display for eg: 100 as 100.

Hope this is clear and I get a solution soon.

I suggest tagging your question with "TOAD" and removing "PL/SQL". — William Robertson, Sep 20 '16 at 09:51
TOAD display numbers and dates according to local settings of your PC, no matter if you do `TO_CHAR(...)` expressions. You have to set this in your Windows "Region and Language" settings. — Wernfried Domscheit, Sep 20 '16 at 09:57

score 2 · Answer 1 · edited Dec 02 '21 at 11:48

2

I'm using MOD for determining decimals.

select test_value, (case when mod(test_value,1) != 0 then 'DECIMAL' else 'NODECIMAL' END) IS_DECIMAL
  from (select 1.5 test_value from dual 
        union all 
        select 100 test_value from dual) test_table

edited Dec 02 '21 at 11:48

ouflak

2,458
10
44
49

answered Dec 02 '21 at 10:15

Tomáš Wojčík

21
2

Aleksej · Answer 2 · 2016-09-20T10:52:26.800

0

If your problem is about the way Toad shows numbers, you can follow the hints in the comments.

If the problem is about the way Oracle shows numbers, converting them to strings, maybe this can help:

SQL> select to_char(1.5, 'TM9') as num from dual union all
  2  select to_char(100, 'TM9') from dual;

NUM
----------------------------------------------------------------
1,5
100

You find much more in the documentation

If you need a way to check whether a number has a decimal part or not, you can simply try:

SQL> with numbers(num) as (
  2        select 1.5 from dual union all
  3        select 100 from dual
  4      )
  5  select case
  6          when floor(num) = num
  7              then to_char(num, 'FM999999') || ' has not a decimal part'
  8          else
  9              to_char(num, 'FM9999D000') || ' has a decimal part'
 10         end as checkString
 11  from numbers;

CHECKSTRING
------------------------------
1,500 has a decimal part
100 has not a decimal part

edited Sep 20 '16 at 10:52

answered Sep 20 '16 at 10:24

Aleksej

22,443
5
33
38

Hey, i know thats the way to do for separate variables. I have a list of values from a table which needs to be displayed differently according to the values. For eg: The list has all kinds of values such as 100, 110.5, 240.2. So what I want to do is display the 100 as 100 but the values with decimal points like 110.500 and 240.200 – Ash Sep 20 '16 at 10:27
hey man, i tried executing what you gave on toad editor and it gives me this error ORA-32033: unsupported column aliasing and it highlights the first line – Ash Sep 20 '16 at 10:48
Which oracle version? If lower than 11, try editing `with numbers(num) as ( select 1.5` into `with numbers as ( select 1.5 as num`. This works well on 11G – Aleksej Sep 20 '16 at 10:50

Oracle plsql how to check if number has decimal points

2 Answers2