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