Search code examples
sqlnumbersdata-conversionvarchar2

How to convert varchar2 to a number in SQL

I have a column which is varchar2 and I need to convert it to a number so I can sum up the column . When I try to convert it I get a - SQL ERROR ORA -0-1722 INVALID NUMBER. I know the issue is I need to convert the varchar2 column (extprice) to a number, but I keep getting an error when trying to do so.

Here's what I have, but I get the error

select to_number(m.extprice) from MA_FLF_COMB m

The column has dollar symbols in it for example: 

extprice
$12.00
$7.00

results I want is to total the 'Extrpice' column

total = 19.00

Solution

  • You can include the dollar sign in the format:

    select to_number('$12.00', '$999.99')