Handling error converting data type varchar to numeric in SQL Server

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 five examples below, we expect to receive the “Error converting data type varchar to numeric” error on three. This is 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. This is why developers become confused when clear decimal values don’t convert.

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, ranging in length post-decimal point. Also, they will not read as numbers in 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 a puzzle for developers – characters that are clearly numbers, but not converting or casting as decimals and failing the ISNUMERIC function. (This is not always reliable. Generally, we will 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. 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 following is a step-by-step way to quickly convert these characters. First, we will extract all the characters on the left side of the decimal place using the LEFT function:

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

Next, 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, 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. 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 have invalid alpha characters in our data set, strip them using RegEx and then apply CAST:

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. Note: The few times I (and other developers) have run across these data have all been private financial 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

Tim Smith

Tim consults for FinTek Development and teaches the course Automating ETL on Udemy. He’s worked with technology since high school, helping his school win its first TCEA award and continues to work in automation, data architecture, back-end development, and smart contract architecture. Tim enjoys testing new technologies early in the diffusion of innovation curve and was an early adopter of NoSQL and smart contract development. He has a blog at http://www.fintekdev.com/ and helps contribute to local technical and financial events in Texas.