Using IF IsNumeric as an Argument

Question

I was wondering how to use IsNumeric in SQL, it is slightly different from VBScript, but I think I managed to get around it, that is:

IF 1 = ISNUMERIC('5675754674') BEGIN ... END 

Would this be a way around this? I really want:

 IF ISNUMERIC('5675754674') BEGIN ... END 

but it gives an error. Example 1 seems to work, but just making sure that I am doing it right, could not find any good resources on the Internet.

Answer 1

There is no boolean in SQL Server. This means that you cannot just say IF (expression) ; you have to compare it with something because it returns true or false in the same sense as you are probably used to in other languages.

Just a preference, but I would rather write it like this:

 IF ISNUMERIC('5675754674') = 1 BEGIN ... END 

There is no way in SQL Server to avoid comparing with 1, as in your second example.

In addition, you should be aware of the weaknesses of ISNUMERIC() - it can give false positives for "numerical" values, such as . , CHAR(9) , e , $ and many other non-numeric strings. If you want to know if there is something integer, for example, it is better to say:

 IF '5675754674' NOT LIKE '%[^0-9]%' BEGIN ... END 

But even this is not a complete and reliable test, because it will return true for values > (2^32)-1 and will return false for negative values.

Another drawback of ISNUMERIC() is that it will return true if the value can be converted to any of the numeric types, which does not match all numeric types. Often people test ISNUMERIC() and then try to apply FLOAT to SMALLINT , and the conversion fails.

In SQL Server 2012, you will have a new TRY_CONVERT() method that returns NULL if the conversion to the specified data type is invalid.

Answer 2

You are right, ISNUMERIC () returns int. You can check the documentation: ISNUMERIC ()

Answer 3

The IF in SQL requires a Boolean expression after IF - see the MSDN link here.

SQL is not suitable for handling casting between types without using something like CAST() or CONVERT() , which means that returning int value ISUNUMERIC() will only be allowed as a boolean using some comparator (=, <,> and etc.) to get a true / false result.

IF ISNUMERIC('5675754674') = 1 is probably the best way to write this in TSQL - it clearly describes the intention “If this value is run through this function, returns 1 (can be evaluated as a numeric data type), then do it. '

With this in mind, you should be aware that ISNUMERIC only checks that the value you pass will evaluate to an available numeric data type. This can lead to problems if the data you use contains scientific notation or other irregularities; see here for more information.