Home » SQL & PL/SQL » SQL & PL/SQL » TO_NUMBER function not working
TO_NUMBER function not working [message #652659] Wed, 15 June 2016 14:46 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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

Re: TO_NUMBER function not working [message #652665 is a reply to message #652660] Wed, 15 June 2016 17:56 Go to previous message
richard_oak
Messages: 8
Registered: May 2016
Junior Member
Thank you!
Previous Topic: "ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data
Next Topic: Removing specific line from file using UTL_FILE
Goto Forum:
  


Current Time: Tue Jul 27 21:02:33 CDT 2021