Skip to content

Posts from the ‘TSQL’ Category

22
Aug

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