CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Control Freak
Control Freak

Reputation: 13213

Using IF IsNumeric as Argument

i was wondering how to use IsNumeric in SQL, it's a bit different from VBScript, but i think i was able to get around it, ie:

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

Would this be a way around it? What I really want to do is:

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

but that gives an error. Example 1 seems to work, but just making sure i'm doing this right, wasn't able to find any good resources online about it.

Upvotes: 10

Views: 28263

Answers (3)

mikurski
mikurski

Reputation: 1363

The IF statement in SQL requires a boolean expression after the IF - see the MSDN reference here.

SQL isn't the best at handling casting between types without using something like CAST() or CONVERT(), which means that the int value ISUNUMERIC() returns will only be resolved as a boolean when you use some comparator (=,<,>,etc) to produce a true/false result.

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

With that in mind, you should be aware that ISNUMERIC only checks that the value you pass in will evaluate to -any- available numeric data type. This can lead to trouble if the data you're running through contains scientific notation or other irregularities; see here for more information.

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

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.

Upvotes: 18

Mario Corchero
Mario Corchero

Reputation: 5565

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

Upvotes: 5

Related Questions