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):
. 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.