Home » SQL & PL/SQL » SQL & PL/SQL » Format a number to double type
Format a number to double type [message #207868] Thu, 07 December 2006 04:03 Go to next message
HoriaC
Messages: 6
Registered: December 2006
Location: Brasov
Junior Member
Hello,
I need to change the format of a number into a SQL query and not using 'set numformat...'.

I tried to use 'to_number (<field name>,'9999.99')' but I get
ORA-01722: invalid number;

It should be similar to
"select Convert(Decimal(17,2), <field name - number type>) from <table_name>" from SQL Server.

Has anybody some suggestions?

thanks,
Horia
Re: Format a number to double type [message #207877 is a reply to message #207868] Thu, 07 December 2006 04:17 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you should get familiar with the TO_CHAR, TO_NUMBER, TO_DATE functions (if that's what you are looking for).
SQL> select to_char('123456', '999,990.00') from dual;

TO_CHAR('12
-----------
 123,456.00

SQL>
Re: Format a number to double type [message #207884 is a reply to message #207877] Thu, 07 December 2006 04:21 Go to previous messageGo to next message
HoriaC
Messages: 6
Registered: December 2006
Location: Brasov
Junior Member


The result should stay double. Let's say we have a double (19,4). I would like to convert this one into a double (17,2) if possible.
Re: Format a number to double type [message #207896 is a reply to message #207884] Thu, 07 December 2006 04:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You want CAST:
create table num_test (col_1  number(12,4));

insert into num_test values (1234.5678);

SQL> select cast(col_1 as number(16,2)) from num_test;

CAST(COL_1ASNUMBER(16,2))
-------------------------
                  1234.57
Re: Format a number to double type [message #207906 is a reply to message #207896] Thu, 07 December 2006 05:12 Go to previous message
HoriaC
Messages: 6
Registered: December 2006
Location: Brasov
Junior Member
that's it! thank you very much!
Previous Topic: doubts in delete statement
Next Topic: order by
Goto Forum:
  


Current Time: Wed Dec 07 06:43:25 CST 2016

Total time taken to generate the page: 0.11780 seconds