how can i convert char data types to numeric datatypes 1

[afybim]

hi all,
i want to convert char data types to numeric data types.i couldn't do it . error msg is 8114."error converting data type varchar to numeric.
What can i do?
thanks

 

[MeanGreen]

You can do this:

select convert(integer,column) as column from Table

Hope this helps.

 

[osjohnm]

Hi

This should work:

select convert(numeric, column) from table

Does the column contain any non numeric data?
eg. '-', '/'

If it does then it becomes harder and you might not be able to convert it if there are non-numeric characters in the column.

John

 

[tlbroadbent]

You can use the IsNumeric function to determine if the value can be converted. IsNumeric returns 1 if the string is numeric or 0 if not.

Example: Select only rows with numeric values.
Select Convert(Integer, ColumnA) As NumericA
From Table
Where IsNumeric(ColumnA) = 1

You can also use the Case function with the IsNumeric function to substitue a value when the column is not numeric or perform the conversion when the column is numeric. Note, I use Cast rather than Convert in this query just to show the syntax. It works the same way.

Select
Case When IsNumeric(ColumnA) = 1
Then Cast(ColumnA As Integer)
Else 0
End As NumericA
From Table Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.