Home » SQL & PL/SQL » SQL & PL/SQL » Help Convert VARCHAR2 to number
Help Convert VARCHAR2 to number [message #36264] Tue, 13 November 2001 14:12 Go to next message
Newie
Messages: 5
Registered: November 2001
Junior Member
Can anyone please help me with this conversion my statement is something like this

"SQL> select SUM(amount) from Accounts "

I keep getting this message

ERROR at line 1:
ORA-01722: invalid number

And the values in the amount are in the format of $123.33AUD

I tried to_number and to_char and they both get the same message about the invalid number...But I need the sum of this column

The amount column is a VARCHAR2 (50).
Any help is appreciated, this is in oracle 8.1.6

----------------------------------------------------------------------
Re: Help Convert VARCHAR2 to number [message #36265 is a reply to message #36264] Tue, 13 November 2001 15:01 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
If all of the values start with a $ and end with 'AUD', you could do:

sum(to_number(replace('$123.33AUD', 'AUD'), '$99999.99'))

Just make sure the number format has enough 9s in it to support your largest value.

----------------------------------------------------------------------
Re: Help Convert VARCHAR2 to number [message #36296 is a reply to message #36265] Wed, 14 November 2001 11:55 Go to previous message
mam.
Messages: 1
Registered: November 2001
Junior Member
"translate" function will work. See Ora Help under "SQL FUNCTIONS".

Details:
SELECT SUM(TO_NUMBER(
TRANSLATE(amount,
' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'.0123456789') ))
FROM Accounts;

----------------------------------------------------------------------
Previous Topic: status invalid for stored procedures
Next Topic: HELP!!! How to preserve data order in table when fetching data in there?
Goto Forum:
  


Current Time: Fri Mar 29 09:55:42 CDT 2024