Scientific Notation and the ISNUMERIC function
Fun problem at work today. Someone was importing some data, and the value 2d4 kept evaluating as 1 using the ISNUMERIC function. The reason why this evaluates true, is because 2d4 is technically a number using scientific notation. The ‘d’ stands for double-precision. 2e4 would also return 1 (exponential value), as would –12, +12, or $12.
In order to correctly check to see if the characters are numeric (in this case, the first 3 characters of a string) you could use this:
DECLARE @Test NVARCHAR(100) = '2D4jlkafkjl' SELECT CASE WHEN LEFT(@Test,3) LIKE '[0-9][0-9][0-9]' THEN CAST(LEFT(@Test, 3) AS INT) ELSE 999 END
Alternatively, if you want to check to see if a string has any letters at all in it, you could just do this:
DECLARE @Test NVARCHAR(100) = '11i111' SELECT CASE WHEN @Test NOT LIKE '%[^0-9]%' THEN 'all numbers' ELSE 'has letters' END