Error converting data type varchar to float

You might get the above error message in different scenarios when trying to convert a string to a float.

The reason for getting this error message is that you are passing as a parameter to the cast or convert SQL Server functions, a varchar expression that is invalid.

Consider the following example:

-----------------------------------------
--Variable declaration and initialization
DECLARE @value AS VARCHAR(50)

SET @value = '12.340.111,91'

--Perform the casting
SELECT Cast(@value AS FLOAT)

--or
--Perform the conversion
SELECT Convert(FLOAT,@value)

-----------------------------------------
If you execute the above code you will get an error message in the following type:

Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to float.
The exact reason for getting the error message in this case is that you are using the comma (,) as a decimal point and also the dots as group digit symbols.

Though SQL Server considers as a decimal point the dot (.). Also when converting a varchar to float you must not use any digit grouping symbols.

In order for the above code to execute you would need to first remove the dots (that is the digit grouping symbols in this case) and then replace the comma with a dot thus properly defining the decimal symbol for the varchar expression.

The code then can be modified as follows:

-------------------------------------------Variable declaration and initialization
DECLARE @value AS VARCHAR(50)

SET @value = '12.340.111,91'

--Prepare the string for casting/conversion to float
SET @value = Replace(@value,'.','')
SET @value = Replace(@value,',','.')

--Perform the casting
SELECT Cast(@value AS FLOAT)

--or
--Perform the conversion
SELECT Convert(FLOAT,@value)
-----------------------------------------

If you execute the above code you will be able to get the string successfully converted to float.

I have also implemented the following function that can be used for the conversion of a string to float (by default I am using the size of 50 bytes for the varchar expression). Here it is the DDL code:
-----------------------------------------
CREATE FUNCTION [dbo].[Varchar2float]
(-- Input Parameter
@inputString VARCHAR(50))
RETURNS FLOAT
AS
BEGIN
--Prepare the string for casting/conversion
SET @inputString = Replace(@inputString,'.','')
SET @inputString = Replace(@inputString,',','.')

--Perform the conversion and return the result
RETURN Cast(@inputString AS FLOAT)
END

-----------------------------------------

Function usage:

-----------------------------------------

--Variable declaration and initialization
DECLARE @value AS VARCHAR(50)

SET @value = '12.340.100,01'

--Call the funtion for performing the casting/conversion
SELECT dbo.Varchar2float(@value)

-----------------------------------------
*Note: Even though you can try changing the regional settings of the PC for setting the dot (.) as the decimal symbol this will only affect the way the data is presented to you when returned from the casting/conversion call. You still have to modify the varchar expression prior to the casting/conversion operation.


If you are interested in SQL Server's In-Memory OLTP, check out my latest software tool "In-Memory OLTP Simulator"!

--
My Latest Projects:


Labels: , ,