Handling error converting data type varchar to numeric in SQL Server

By:   |   Comments (16)   |   Related: 1 | 2 | > Data Types


Problem

We've been importing data into VARCHAR columns to verify valid character types before moving into our final destination table and we ran across some decimal values that wouldn't CAST or CONVERT even though they appeared to be decimal values (other decimal values from the same source converted without errors). We received the message "Error converting data type varchar to numeric" and even when we tried to import them as numbers they also failed. In addition, all values failed the ISNUMERIC function even though the values look numeric (like 1.00) and when we copy these values into Google Spreadsheets and run functions on them, we get numerical answers. How can we load these problem values?

Solution

Here is an example of the issue I was facing. In the below screenshot the data looks correct, but when I checked to make sure the values were numeric using a CAST function I got the following error message.

select data from table
error converting data type varchar to numeric

Here are some of the things I noticed:

  1. They appear as numerical characters, yet don't convert.
  2. If we copy the values directly and do a direct SELECT CAST('1.00000' AS DECIMAL(22,8)), they convert without error.
  3. If they come with extra spaces, no trimming function works to remove the error.

We seldom stumble on these types of data, but they can create encumbrances for developers, so it's good to know a work-around when transforming these VARCHARs into numerical data points. What differs about these data, compared to other times when facing issues with converting numerical VARCHARs to numerical data points is that all of them will fail the ISNUMERIC (for verifying), CAST, CONVERT, TRY_CONVERT and TRY_PARSE functions (the latter two returning NULLs). In other cases, when converting VARCHARs to numerical data points, we can use these other functions to solve the problem.

In the below five examples, we expect to receive the error "Error converting data type varchar to numeric" on three of these due to characters that aren't convertible to decimals. We would not, however, expect to get this error on the second value (1.000000), yet these data will be formatted this way, which is why developers can become confused as to why clear decimal values aren't converting.

SELECT CAST('Dog' AS DECIMAL(22,8))
SELECT CAST('1.000000' AS DECIMAL(22,8))
SELECT CAST('$1.00' AS DECIMAL(22,8))
SELECT CAST('765e1' AS DECIMAL(22,8))
SELECT CAST('22' AS DECIMAL(22,8))

These rare values will always be decimal characters, with a range of length post decimal point, and will not read as numbers in both C# or PowerShell, even though they are if you were to copy them in a tool like Excel or Google Spreadsheets and run a mathematical function on them (like SUM or AVG). This is part of what can create a puzzle for developers - characters which clearly are numbers, not converting or casting as decimals and failing the ISNUMERIC function (which is not always reliable, but will generally see numbers like 1.00 as valid).

The below example populates data that we could normally use the CAST(ExampleColumn AS DECIMAL(22,8)) for converting to a numerical column, and these example values look no different than these rare character values; the difference is that in the latter case, the casting will fail.

CREATE TABLE VarcharExample (
	ExampleColumn VARCHAR(100)
)

INSERT INTO VarcharExample
VALUES ('20.0000')
	, ('357.500')
	, ('1226.00')
	, ('71.0')
	, ('36987.200000')
	, ('45.16710')
	, ('9645.00')

---- Note how this works:
--SELECT CAST(ExampleColumn AS DECIMAL(13,6))
--FROM VarcharExample


---- Note how ISNUMERIC knows these are numbers:
--SELECT ExampleColumn
--FROM VarcharExample
--WHERE ISNUMERIC(ExampleColumn) = 1


---- When running into numbers like the above type - clear decimal values, yet failing every CAST/CONVERT function - the below is quick, effective work-around
---- Note that each column is step-by-step
SELECT LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) PreDecimal
	, SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),6) PostDecimal
	, LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) + '.' + SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),6) FormattedVarchar
	, CAST(LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) + '.' + SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),3) AS DECIMAL(13,6)) CastedNumeric
FROM VarcharExample


DROP TABLE VarcharExample

How to fix error converting data type varchar to numeric

The step-by-step way to quickly convert these characters is to extract all the characters on the left side of the decimal place, seen in the below T-SQL code using the LEFT function:

LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) PreDecimal

Next, we want to extract the characters after the decimal place, using both the SUBSTRING function to parse the values on the right side of the decimal.

SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),6) PostDecimal

Third, we add the decimal point between the PreDecimal and PostDecimal characters:

LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) + '.' + SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),6) FormattedVarchar

We can wrap our CAST function around the PreDecimal, decimal, and PostDecimal and SQL Server will cast it without an error:

CAST(LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) + '.' + SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),3) AS DECIMAL(13,6)) CastedNumeric

Finally, if we do have invalid alpha characters in our data set, we can strip them using RegEx and then apply our CAST as follows:

CREATE TABLE VarcharExample (
	ExampleColumn VARCHAR(100)
)

--- Using three invalid values:
INSERT INTO VarcharExample
VALUES ('20.0000')
	, ('357.500')
	, ('1226.00')
	, ('71.0')
	, ('36987.200000')
	, ('45.16710')
	, ('9645.00')
	, ('Dog')
	, ('$1.00')
	, ('12E33')


;WITH ClearAlphas AS(
	SELECT ExampleColumn
	FROM VarcharExample
	WHERE ExampleColumn NOT LIKE '%[A-za-z$]%'
), ParseNumerics AS (
	SELECT CAST(LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) + '.' + SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),3) AS DECIMAL(22,8)) CastedNumeric
	FROM ClearAlphas
)
SELECT *
FROM ParseNumerics

Here we can see that the two rows that contain "Dog", "$1.00" and "12E33" are excluded.

data convert output

Running into these situations won't be common (which is great), yet it's useful to have a quick way to parse them, as we don't want to spend a lot of time trying to convert characters to their correct format. Note that the few times I've run across these data, and other developers have asked about them, have all been financial sources (private data sources).

Next Steps
  • When receiving the error "Error converting data type varchar to numeric" and the values are clearly numeric, use this approach on the data.
  • Keep this tip in mind if you ever run into decimal data that won't CAST or CONVERT.
  • Read more tips about importing data


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Tuesday, October 27, 2020 - 9:24:02 PM - Luke Back To Top (86703)
This issue is most likely because there is non-space or tab within the field maybe "\r" has been loaded ( especially if it is the last field loaded from a file ). I would recommend checking what characters(non-viewable) is in the field

Thursday, September 17, 2020 - 3:35:53 PM - Alex Tanchyn Back To Top (86500)
This is unbelievably helpful and has relieved me of some major headaches

Friday, February 7, 2020 - 9:48:33 PM - Peter Dobbs Back To Top (84257)

This does it perfectly in one step.

dbo.udf_GetDecimalNegatives('-12.9999')

 

CREATE FUNCTION [dbo].[udf_GetDecimalNegatives]
(
  @strAlphaNumeric VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
  DECLARE @intAlpha INT
  SET @intAlpha = PATINDEX('^-?[0-9]\d*(\.\d+)?$', @strAlphaNumeric)
  BEGIN
    WHILE @intAlpha > 0
    BEGIN
      SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
      SET @intAlpha = PATINDEX('^-?[0-9]\d*(\.\d+)?$', @strAlphaNumeric )
    END
  END
  RETURN ISNULL(@strAlphaNumeric,0)
END

Friday, August 9, 2019 - 7:26:27 AM - kiran vemasani Back To Top (82012)

Error converting data type varchar to numeric.

then use

cast(column  as float) for decimal value

Thursday, April 11, 2019 - 8:46:46 AM - Nick R Back To Top (79531)

function TRY_CONVERT is the simple solution for most cases

Friday, January 25, 2019 - 8:46:48 AM - jmoden Back To Top (78880)

I'm with Brenda... none of the examples resulted in errors for me.  Can you explain?

Monday, May 14, 2018 - 11:23:50 AM - Andrew Back To Top (75939)

 

Hello, 

 

My numbers are negative, so the below code does not work for me - can you please tell me how to do this for negatvie numbers?>

 

LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) PreDecimal



Thanks!
Andrew

Tuesday, January 23, 2018 - 9:48:42 AM - Brenda Back To Top (75020)

I am confused. All of your examples of converted/cast a numeric value worked without error for me using SQL 2008 R2.  Was this supposed to give an error - SELECT CAST('1.000000' AS DECIMAL(22,8)) ? Or this give an error SELECT CAST(ExampleColumn AS DECIMAL(13,6)) FROM VarcharExample ? Neither errored for me.

 

Thursday, October 26, 2017 - 10:44:18 AM - Tim Back To Top (68825)

 @Tim

We can add to the second CTE (ParseNumerics) the below:

 

 

[...]

UNION ALL

SELECT ExampleColumn AS CastedNumeric

FROM ClearAlphas

WHERE ExampleColumn NOT LIKE '%.%' 

 

 

This will still filter out non-numerics by alpha chacters, but will also include numbers that don't have a decimal in them, as the first part of the second CTE ONLY includes decimal numbers.

 

@Khan

 

From the appearance of your error, it appears that you're trying to filter a varchar to numeric.  The returned value of the above query is a varchar value, not a numeric value.  In order to make it numeric, we would need to:

 

 

[...]

SELECT CAST(CastedNumeric AS NUMERIC)

FROM ParseNumerics

 

 

Thursday, October 26, 2017 - 9:50:59 AM - Khan Back To Top (68820)

 Error converting data type varchar to numeric for this code: OR COALESCE(scm.[BranchID],'') <> scmN.BranchID

 

Tuesday, October 17, 2017 - 2:14:28 PM - Tim Back To Top (68481)

 This is not a comment but what do I do if there are no decimals involved. I already tested the one column in question and there are no decimals involved. 

 

Sunday, April 3, 2016 - 10:45:22 AM - Mark Back To Top (41128)

 Excellent Article.  


I was trying to import data from an outside vendor and encounterered this issue because of a "," in a field. for example 1,200.00   I tested wit ISNUMERIC and every row passed that test.  My fix was a little easier than using CHARINDEX and SUBSTRING.  I simply updated the column to remove the ",":

UPDATE <<TABLENAME>>

SET QUANTITYFIELD = REPLACE(QUANTITYFIELD , ',','')

 

I guess I was lucky in that I only had the column issue. The solution you provided would be my go to solution if I was not able to easily identify the issue or if I had multiple issues.

 

Thanks

 

 

Wednesday, March 23, 2016 - 11:59:55 AM - Derek Back To Top (41039)

 This was a life saver.  I had commas in the thousand place so i also had to do a simple replace function in a subquery.  Thanks so much for posting this!

Friday, September 4, 2015 - 10:25:37 AM - PhyData DBA Back To Top (38600)

Did you ever find the character that was being written to the Varchar field that was making your data not numeric?

I ask because if the character was a LF or CR character that was being inserted by some buggy code your solution could cause data loss.

A Cut and paste from SSMS or Visual Studio would not grab the LF or CR or any data after it.

So 3.09543 could end up being turned into 3.0 by your solution.  Since your data destination has 6 numbers after the decimal that might be important to what it is being used for.

Thursday, September 3, 2015 - 8:04:15 PM - Rick Willemain Back To Top (38597)

Well written article on a time-killer sort of problem that you might find.  Thank you ! 

Thursday, September 3, 2015 - 4:53:09 AM - jacek Back To Top (38592)

in SQL Server 2012 and above you may use:

;with q as
(
 select
 try_convert(DECIMAL(22,8), ExampleColumn) is_conv, ExampleColumn
 from VarcharExample
)
select 
  cast(ExampleColumn AS DECIMAL(22,8)) from q where is_conv is not null

 

Regards














get free sql tips
agree to terms