ISNUMERIC maybe…
Wednesday, 30. September 2009 10:00
So the other day I was working on a T-SQL query that I was using to take data from generic data types and insert it into a strongly typed table. One of the columns was moving from an nvarchar(255) to an decimal data type. As some of the rows had character data in it, I wrote something like this:
CASE ISNUMERIC(Col1)
WHEN 1 THEN CAST(Col1 as decimal(9,4))
ELSE NULL
END as Col1
However when I ran the query, I received this error:
Error converting data type nvarchar to numeric.
It took me awhile to figure this one out as you can see I”m using ISNUMERIC to check before I convert it to a number, seems pretty straight forward, so what’s the bid deal?
The big deal is that I needed to read the manual…according to the ISNUMERIC function in BOL:
ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see Using Monetary Data.
It turned out I had a row that had a value of a single decimal point and no numbers which caused the problem. So remember, understand what the functions you use actually do, not what you think they do and test!
Category:SQL Server, T-SQL | Comments (3) | Author: Aaron Lowe