| Help Convert VARCHAR2 to number [message #36264] |
Tue, 13 November 2001 14:12  |
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  |
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 |
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;
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
