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.
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!)
