Fun with ISNUMERIC

For some small value of “Fun”

July 31, 2013

This bit me today, so I thought I would put up a post about it. See, without looking at the answers below first , whether you can guess which of the following queries return 0 and which return 1 in SQL Server:

SELECT '.',   ISNUMERIC('.')
SELECT '+',   ISNUMERIC('+')
SELECT '-',   ISNUMERIC('-')
SELECT '*',   ISNUMERIC('\*')
SELECT '/',   ISNUMERIC('/')
SELECT 'E',   ISNUMERIC('E')
SELECT '0E0', ISNUMERIC('0E0')
SELECT '$',   ISNUMERIC('\$')

The answers are below (scroll down):















Results:

.    1
+    1
-    1
*    0
/    0
E    0
0E0  1
$    1

The SQL Server help for ISNUMERIC clearly states “ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).” I thought that means it would consider +1 or 0.1 as numeric. Never in a million years would I have guessed that it meant “a plus (+) sign or a period (.) by itself is considered numeric.”

You have been warned.