I need SQL statement that can cast string (A column in a table) to double or int. I used this select Cast(Amount_Paid as int) from contractpayment but wasn't working. Amount paid is in string data type for a reason, Plz I need ur help

I assume you're using MS-SQL in which case you could try rounding the column first before the cast as maybe the column is stored in a decimal format

select cast(round(amount_paid, 0) as int) as "AmountPaid"
 from contractpayment

Alternatively, if your intention was to Cast as double then you should use the Double qualifier in the Cast() function [i.e. Cast(<Column Name> as Double)]


I don't know if it's relevant but Oracle has a built function for converting varchar2 to a number as follows:

to_number('1210.73', '9999.99') would return the number 1210.73
to_number('546', '999') would return the number 546
to_number('23', '99') would return the number 23

So your example would be written as follows:

select to_number(amount_paid '9,999,999') as "AmountPaid"
  from contractpayment

Am using MySQL 5.1. I have used all these functions but none worked out for. I don't know what is the problem. Is still complain that I have not used the right MySQL syntax for cast.

It could be that you are not using one of the correct type identifiers in your Cast() function. From what I can see in the documentation, "int" is not one of the defined types

I got this from the MySQL Developer site http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html:

CAST(expr AS type), CONVERT(expr,type), CONVERT(expr USING transcoding_name)

The CAST() and CONVERT() functions take a value of one type and produce a value of another type.

The type can be one of the following values:
  BINARY[(N)]
  CHAR[(N)]
  DATE
  DATETIME
  DECIMAL
  SIGNED [INTEGER]
  TIME
  UNSIGNED [INTEGER]

I guess you should also ensure that the contents of the column can be safely converted in that it doesn't contain any non numeric characters such as currency indicators, semi colons etc. and also ensure that the column in question is not null.

What is the output if you just do a straightforward select without a Cast() or Convert()?

Thanks so much Fdeveloper I was casting to data types not supported. SO I used decimal and it worked.