Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Handling error converting data type varchar to numeric in SQL Server


By:   |   Read Comments (8)   |   Related Tips: 1 | 2 | More > Data Types

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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


Last Update:


signup button

next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 @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

 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

 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 03, 2016 - 10:45:22 AM - Mark Back To Top

 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

 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 04, 2015 - 10:25:37 AM - PhyData DBA Back To Top

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 03, 2015 - 8:04:15 PM - Rick Willemain Back To Top

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


Thursday, September 03, 2015 - 4:53:09 AM - jacek Back To Top

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


Learn more about SQL Server tools