ISNUMERIC maybe…
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!
My friend George wrote the following post IsNumeric, IsInt, IsNumber http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/isnumeric-isint-isnumber
Some more fun
DECLARE @S VARCHAR(50)
SET @S = CHAR(9) — @S NOW CONTAINS THE TAB CHARACTER
SELECT ISNUMERIC(@S), ISNUMERIC(CHAR(9)),ISNUMERIC(’1D2′),ISNUMERIC(‘D’)
Hello,
The only way I found to check that a string contains only numbers is the second function I published on my blog :
http://blog.developpez.com/elsuket/p7726/snippets/une-fonction-d-extraction-des-nombres-co/
I’ve recently written a blog post to "play" around with this system function.
isnumeric != isnumber
http://louiebaopro.spaces.live.com/blog/cns!D0B9A22DC37EAE4F!130.entry