Skip to content

August 22, 2014

Scientific Notation and the ISNUMERIC function

by Scott Newman

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 

Read more from SQL Server, TSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

%d bloggers like this: