5 Ответы Последний ответ: 08.05.2007 1:45, автор: ushitaki

# Conversion of NUMBER to DECIMAL

I am querying a field that is a NUMBER(3,1) data type and I need to have it returned as a DECIMAL with two decimal points. For example, 5 would be returned as 5.00.

Thanks,
Sushi Lover
• ###### 1. Re: Conversion of NUMBER to DECIMAL
try like this...

SQL> select sal,to_char(sal,'99999999.99') sal from emp;

SAL SAL
---------- ------------
1250 1250.00
3200 3200.00
800 800.00
882 882.00
1764 1764.00
1377 1377.00
3279 3279.00
1377 1377.00
• ###### 2. Re: Conversion of NUMBER to DECIMAL
That converts it to a CHAR. Is it possible to convert it to a DECIMAL?
• ###### 3. Re: Conversion of NUMBER to DECIMAL
CAST(col AS NUMBER(4,2))

Or are you talking about display formatting? If so it will depend on the client application. For example in SQL*Plus,
```SQL> desc t
Name                    Null?    Type
----------------------- -------- ----------------
N                                NUMBER(3,1)

SQL> select * from t;

N
----------
12.3

1 row selected.

SQL> col n format 990.00
SQL> r
1* select * from t

N
-------
12.30

1 row selected.```
or more portable, avoiding hardcoding the decimal point character:
```SQL> col n format 990D00
SQL>
SQL> r
1* select * from t

N
-------
12.30

1 row selected.```
• ###### 4. Re: Conversion of NUMBER to DECIMAL
DECIMAL and NUMBER are exactly the same thing in oracle. You simply need to format the output
``````michaels>  col dec format a30
michaels>  col num format a30
michaels>  col sal format 9999.00

michaels>  SELECT DUMP (CAST (sal AS DECIMAL (5, 1))) DEC,
DUMP (CAST (sal AS NUMBER (5, 1))) num,
sal
FROM emp
/
DEC                            NUM                                 SAL
------------------------------ ------------------------------ --------
Typ=2 Len=2: 194,9             Typ=2 Len=2: 194,9               800.00
Typ=2 Len=2: 194,17            Typ=2 Len=2: 194,17             1600.00
Typ=2 Len=3: 194,13,51         Typ=2 Len=3: 194,13,51          1250.00
Typ=2 Len=3: 194,30,76         Typ=2 Len=3: 194,30,76          2975.00
Typ=2 Len=3: 194,13,51         Typ=2 Len=3: 194,13,51          1250.00
Typ=2 Len=3: 194,29,51         Typ=2 Len=3: 194,29,51          2850.00
Typ=2 Len=3: 194,25,51         Typ=2 Len=3: 194,25,51          2450.00
Typ=2 Len=2: 194,31            Typ=2 Len=2: 194,31             3000.00
Typ=2 Len=2: 194,51            Typ=2 Len=2: 194,51             5000.00
Typ=2 Len=2: 194,16            Typ=2 Len=2: 194,16             1500.00
Typ=2 Len=2: 194,12            Typ=2 Len=2: 194,12             1100.00
Typ=2 Len=3: 194,10,51         Typ=2 Len=3: 194,10,51           950.00
Typ=2 Len=2: 194,31            Typ=2 Len=2: 194,31             3000.00
Typ=2 Len=2: 194,14            Typ=2 Len=2: 194,14             1300.00

14 rows selected.``````
• ###### 5. Re: Conversion of NUMBER to DECIMAL
In Oracle DECIMAL is alias of NUMBER.

If you want to display with two decimal points format
you ought to convert it to character for displaying
or set your tools to format columns.
```SQL> create table test(num number(3,1));

Table created.

SQL> insert into test values (12.1);

1 row created.

SQL> column num42 format 99.00
SQL> column dec42 format 99.00
SQL> select num,cast(num as decimal(4,2)) as dec
2        ,num as num42, cast(num as decimal(4,2)) as dec42
3  from test;

NUM        DEC  NUM42  DEC42
---------- ---------- ------ ------
12.1       12.1  12.10  12.10```