Reputation: 3

How to convert character column to numeric in SQL?

Example of values in the column are:

$3,459.09
$98.00
$6,789,123.15
$5,435.00

I can remove the dollar sign by using a substring, but I am not able to find a way to convert character to numeric.

I tried a couple of options 1. to_number(billed_amount) end as paid_amount_value 2. cast ( billed_amount as INT) as paid_amount_value

Error: for 1st:

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

for2nd:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

Upvotes: 0

Answers (3)

Reputation: 757

Your column values do have double values so you have to convert them to decimal. You can use To_number it will take care of it..

If you observe in your error message the values do have spaces in between. So, Try to remove them then cast their datatype.

to_number(billed_amount) as paid_amount works for you to_number

To replace empty spaces you can use replace replace

Upvotes: 0

Reputation: 1

If you are not using double as your datatype use double. You need to remove comma's and empty spaces if present

Upvotes: 0

Reputation: 31993

use TO_Number and format '$999,999,999.99'

   select TO_Number('$3,459.09','$999,999,999.99') from dual

so in you case

select TO_Number(column_name,'$999,999,999.99') from table_name

Upvotes: 2