SQL SERVER IF ISNUMERIC
Posted by karuta em julho 10, 2014
There is no boolean in SQL Server. This means you can’t just say IF (expression); you must compare it to something, because it does return true or false in the same sense as you’re probably used to in other languages.
Just a preference, but I would prefer to write it this way:
IF ISNUMERIC('5675754674') = 1 BEGIN ... END
There is no way in SQL Server to avoid the comparison to 1, as in your second example.
Also as an aside you should be aware of the weaknesses of ISNUMERIC() – it can give false positives for “numeric” values such as ., CHAR(9), e, $ and a host of other non-numeric strings. If you want to know if something is an integer, for example, better to say:
IF '5675754674' NOT LIKE '%[^0-9]%' BEGIN ... END
But even that is not a complete and valid test because it will return true for values > (2^32)-1 and it will return false for negative values.
Another downside to ISNUMERIC() is that it will return true if the value can be converted to any of the numeric types, which is not the same as all numeric types. Often people test for ISNUMERIC() and then try to cast a FLOAT to a SMALLINT and the conversion fails.
In SQL Server 2012 you will have a new method called TRY_CONVERT() which returns NULL if the conversion to the specified data type is not valid.