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

Posted on by Jennifer Stirrup

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 thoughts on “Error converting data type varchar to numeric where ISNUMERIC finds only numbers”

  1. Pingback: ISNUMERIC And Unexpected Results – Curated SQL

  2. 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.

    Reply

    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.

      Reply

      1. Okay. But you posted about NOT LIKE as though it was the right solution, so I thought that’s where you ended up.

Leave a Reply