| Re: Help Convert VARCHAR2 to number [message #36267] |
Tue, 13 November 2001 23:02  |
Rob Baillie
Messages: 33
Registered: November 2001
|
Member |
|
|
Personally, I'd go for:
SELECT SUM( TO_NUMBER( REPLACE( REPLACE( amount, 'AUD' ), '$' ), '999,999,999,999.00') )
FROM accounts
But you may still get invalid numbers, or numeric expected, or suchlike, if a value in 'amount' doesn't conform to the format mask.
----------------------------------------------------------------------
|
|
|
|
| Re: Help Convert VARCHAR2 to number [message #36280 is a reply to message #36267] |
Wed, 14 November 2001 07:26  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Sure, you can strip the $ out to on a replace. I had left it in since the mask supports it.
Obviously, there is some data out there (maybe some string other than 'AUD' following the amount) that is causing a problem.
Maybe he could try:
select * from accounts
where instr(amount, 'AUD') = 0
to see if there are some other trailing strings out there.
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
