OraFAQ Forum: SQL & PL/SQL » TO_NUMBER function not working

Home » SQL & PL/SQL » SQL & PL/SQL » TO_NUMBER function not working

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: TO_NUMBER function not working [message #652660 is a reply to message #652659]

Wed, 15 June 2016 15:01

Barbara Boehmer
Messages: 9106
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

richard_oak
Messages: 8
Registered: May 2016

Junior Member

Thank you!

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 28 09:53:22 CDT 2025