Handling error converting data type varchar to numeric in SQL Server

By: Tim Smith   |   Updated: 2015-09-03   |   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.

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.

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

About the author

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