Error converting data type varchar to numeric where ISNUMERIC finds only numbers

Posted by

I am writing some SQL to form the basis of views to pull data from a Microsoft SQL Server source into Azure SQL Database, and then the data would go to Power BI. The data was all presented in string format initially (not my data, not my monkeys, not my circus), and I wanted to correct the data types before the data got into Power BI.

I noted that one of the columns failed to convert VARCHAR to DECIMAL. The error message is below, and it’s usually fairly easy to sort:

Error converting data type varchar to numeric

Normally, I’d use ISNUMERIC to identify the rows that fail to have a value in that column that could be converted to a number. Then, I could identify the value, and then I could replace or exclude it, as required.

However, on this occasion, using ISNUMERIC failed to identify any columns as being non-numeric. ISNUMERIC returned all of the rows as TRUE, and that confused me. I knew that something was triggering the CONVERT instruction to go wrong.

parrot-1070624_1920
Confused? yes, me too. Credit: https://pixabay.com/en/users/lovelikeahurricane-720440/

I ran a quick query, ordering the column in ASCENDING order, while running the original offending query that failed. This showed that the query stopped at the value 9.45. I then ran another query that returned the rows, where the value was greater than 9.45, and ordered the results.

In this result set, the value came through as follows:

9.450000001e-05

Aha! This explained why SQL Server could convert the value to numeric, because of the scientific notation used when the values are very large or very small.

Now, I ran my query again, using a NOT LIKE (which I also do not like!)

WHERE [My Column Name] NOT LIKE ‘%e%’
Now, out of my large record set, I got one offending row with the scientific notation, out of millions of rows. At least I had something to work with now; I could remove the data, run an update, or work with the scientific notation.
I hope that helps someone!

4 comments

  1. Why aren’t you using TRY_CAST (or TRY_CONVERT, or TRY_PARSE)? ISNUMERIC accepts all sorts of characters (including currency symbols, +, -, \, and others) that may not be valid for your target datatype. The newer functions actually test for what you want: whether the conversion to your target datatype will succeed.

    1. Thanks Gil! yes, I’m aware of that. It’s a separate topic for another post. I try to keep the posts focused. I’ve had feedback from readers that my posts are too long, so I’m trying to produce more focused posts.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s