| TO_NUMBER function not working [message #652659] |
Wed, 15 June 2016 14:46  |
 |
richard_oak
Messages: 8
Registered: May 2016
|
Junior Member |
|
|
Hi,
I'm executing following simple query in 11g and 12c and in both a "ORA-01722: invalid number" error is displayed.
The result of this query is supposed to be $25000.53.
SELECT TO_NUMBER('25000.53','$99G999D99') NUM FROM DUAL;
In order for this query to work, I have to format the number as follows, but then what would be the point of using the TO_NUMBER fuction? Or maybe a configuration of my SQL Developer my be affecting?
SELECT TO_NUMBER('$25,000.53','$99G999D99') NUM FROM DUAL;
Thanks,
Rich
|
|
|
|
| Re: TO_NUMBER function not working [message #652660 is a reply to message #652659] |
Wed, 15 June 2016 15:01  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The purpose of the TO_NUMBER function is to convert a string to a number. To do that the string and the format that follows it must match. As you have discovered, you can make the number the same as the format. Alternatively, you make the format the same as the number, which is a more common way to do things, as shown below.
SCOTT@orcl_12.1.0.2.0> SELECT TO_NUMBER('25000.53','99999D99') NUM FROM DUAL;
NUM
----------
25000.53
1 row selected.
If you want to convert a number to a string that includes a dollar sign and comma, then you should use TO_CHAR and remove the quotes from around the number, as shown below.
SCOTT@orcl_12.1.0.2.0> SELECT TO_CHAR(25000.53,'$99G999D99') NUM FROM DUAL;
NUM
-----------
$25,000.53
1 row selected.
[Updated on: Wed, 15 June 2016 15:05] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
