Error converting data type varchar to Float.

By
roopesh.valluru
-
12741

Friends,

Sometimes you need to convert the data of a column in SQL server table from VARCHAR(String) to FLOAT. When I was working on the similar one today i got an error saying that

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to Float.

PFB the code I have written  –

SELECT col1, CAST(col2 AS FLOAT) as col2 FROM #DaTable

I was getting this error because there is some data like NULLS and non numeric values which cannot be converted to FLOAT datatype. To handle this kind of error we have to use the CASE Statement. PFB the converted code which converts VARCHAR column to FLOAT column.

SELECT col1,  
	CASE WHEN ISNUMERIC(col2) = 1
		THEN CAST(col2 AS FLOAT) 
		ELSE NULL 
	END as col2
FROM #tableName

In the above example at first point I am checking whether the data is NUMERIC or not using ISNUMERIC() function and if the data is non numeric then I am replacing the value with NULL as I cannot convert it to FLOAT type and then casting the data ONLY when it is NUMERIC to FLOAT.
Hope this helps !!
Regards,
Roopesh Babu V

RELATED ARTICLESMORE FROM AUTHOR