Home » SQL & PL/SQL » SQL & PL/SQL » Re: Help Convert VARCHAR2 to number
Re: Help Convert VARCHAR2 to number [message #36267] Tue, 13 November 2001 23:02 Go to next message
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 Go to previous message
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.

----------------------------------------------------------------------
Previous Topic: leading 0's trimmed from number column
Next Topic: Help on select needed
Goto Forum:
  


Current Time: Fri Aug 14 08:21:36 CDT 2020