Error converting data type varchar to float

Error converting data type varchar to float

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
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:



Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.

10 thoughts on “Error converting data type varchar to float

  1. Shanthi.Gangatharan

    I need to convert nvarchar datatype of my table column to float

    But during this operation

    the below error message returned by the sql server 2005

    'tbl_user_mast' table – Unable to modify table. Error converting data type nvarchar to float.

  2. Elena

    Hi )
    Can you help a beginner with StoredProcedure? )

    I have a table with columns (type varchar) as example:
    D1-D2-D3
    3 – б –
    2 – – 8
    1 – –
    I need to get the sum of each column
    if only there were numbers – then everything is OK
    write

    SELECT
    SUM (CAST (Round (D1, 2) As decimal (5,2))) As D1
    FROM

    but i have a letters in some columns, so can't convert string to float

    (if in columns is a letter,then SUM is equivalent to = 8 )

    so i try to use CASE


    SELECT
    SumD2 =
    CASE D2
    WHEN 'б' then 8
    ELSE
    SUM (CAST (Round (D2, 2) As decimal (5,2)))
    END
    FROM

    and here's a "gift" – Column dbo.Zadanie.D2 is invalid in the select list because it is not contained in either an aggregate function or ORDER BY clause

    tell my what am I doing wrong?

  3. Artemakis Artemiou [MVP]

    Hi Elena,

    You are getting this error because you are using an aggregate (SUM) in the one part of the CASE statement and you are not using an aggregate in the other part.

    You could use a GROUP BY or an ORDER BY clause but you would not get the result you expect.

    It is difficult to implement the requested logic with a single T-SQL statement because queries read record-by-record the contents of a table. Whenever a character occurs you would need to stop the aggregation and set the sum to 8.

    Though, by the time you are using a stored procedure as you said, you can use multiple statements for implementing the required logic.

    To this end I would suggest trying the following code in the stored procedure you are building:

    DECLARE @d1 AS INT
    DECLARE @d2 AS INT
    DECLARE @d3 AS INT

    DECLARE @sumd1 AS FLOAT
    DECLARE @sumd2 AS FLOAT
    DECLARE @sumd3 AS FLOAT

    SET @d1=(SELECT COUNT(*)
    FROM testtable
    WHERE Isnumeric(d1) = 0
    AND d1 IS NOT NULL)

    SET @d2=(SELECT COUNT(*)
    FROM testtable
    WHERE Isnumeric(d2) = 0
    AND d2 IS NOT NULL)

    SET @d3=(SELECT COUNT(*)
    FROM testtable
    WHERE Isnumeric(d3) = 0
    AND d3 IS NOT NULL)

    IF @d1 = 0
    SET @sumd1=(SELECT SUM(CAST(Round (d1, 2) AS DECIMAL (5, 2)))
    FROM testtable)
    ELSE
    SET @sumd1=8

    SELECT @sumd1

    IF @d2 = 0
    SET @sumd2=(SELECT SUM(CAST(Round (d2, 2) AS DECIMAL (5, 2)))
    FROM testtable)
    ELSE
    SET @sumd2=8

    SELECT @sumd2

    IF @d3 = 0
    SET @sumd3=(SELECT SUM(CAST(Round (d3, 2) AS DECIMAL (5, 2)))
    FROM testtable)
    ELSE
    SET @sumd3=8

    SELECT @sumd3

    If the table contained an ID column along with the value columns maybe another logic could be implemented but by the time the table contains only value columns the above is the workaround I would suggest.

    Cheers,
    Artemis

  4. Elena

    Thank you for your quick and detailed response so)
    I did so:

    SELECT

    SUM(CASE D1 WHEN 'б' THEN 8 WHEN 'о' THEN 8 WHEN 'от' THEN 8 WHEN 'к' THEN 8 ELSE CAST(ROUND(D1,2) AS DECIMAL(5,2)) END) as SumD1,
    SUM(CASE D2 WHEN 'б' THEN 8 WHEN 'о' THEN 8 WHEN 'от' THEN 8 WHEN 'к' THEN 8 ELSE CAST(ROUND(D2,2) AS DECIMAL(5,2)) END) as SumD2,
    SUM(CASE D3 WHEN 'б' THEN 8 WHEN 'о' THEN 8 WHEN 'от' THEN 8 WHEN 'к' THEN 8 ELSE CAST(ROUND(D3,2) AS DECIMAL(5,2)) END) as SumD3,

    FROM dbo.PrZad

    WHERE (StaffID = @StaffId) AND (MONTH(PlanDataOtch) = @Месяц) AND (YEAR(PlanDataOtch) = @Год) AND (CodZak <> 24)

    It works!
    Thank you!!

  5. Shailu

    I also incurred same error msg "Error converting data type nvarchar to numeric", solved converting numeric column to varchar

    Problem: TableA.EmpID = TableB.EmployeeID

    Here TableA.EmpID is varchar datatype and in other end TableB.EmployeeID was in Decimal datatype. I converted TableB.EmployeeID to varchar datatype.

    Solution: TableA.EmpID = Cast (TableB.EmployeeID as Varchar)