Why IsNumeric() returns unexpected results?

IsNumeric() is a handy function that returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0.

Reading carefully, it can sometimes return unexpected value. Book Online notes that it returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).

There are also some accounting symbols that evaluates to a number, hence the function returns 1. The most commonly forgotten reason is "Scientific Notation". The letters "e" and "d" are used for different numeric notations. These letters, as is, do not evaluate as a number and the function will return 0.

As these samples shows:

SELECT Expression = '''d''', IsItaNumber = ISNUMERIC('d')
SELECT Expression = '''e''', IsItaNumber = ISNUMERIC('e')

Here are the results:

Expression IsItANumber
---------- -----------
'd'        0

Expression IsItANumber
---------- -----------
'e'        0

Included in numbers, however will return a different result

SELECT Expression = '''097d89''',  IsItANumber = ISNUMERIC('097d89')
SELECT Expression = '''489e239''', IsItANumber = ISNUMERIC('489e239')

Here are the results:

Expression IsItANumber
---------- -----------
'097d89'   1

Expression IsItANumber
---------- -----------
'489e239'  1

Enter Regular Expression...

SELECT Expression = '''097d89''',  IsItANumber = ISNUMERIC('097d89'),
     RegExIsItANumber = case when '097d89' NOT LIKE '%[^0-9]%' then 1 else 0 end

SELECT Expression = '''489e239''', IsItANumber = ISNUMERIC('489e239'), 
     RegExIsItANumber = case when '489e239' NOT LIKE '%[^0-9]%' then 1 else 0 end

Here are the results:

Expression IsItANumber RegExIsItANumber
---------- ----------- ----------------
'097d89'   1           0

Expression IsItANumber RegExIsItANumber
---------- ----------- ----------------
'489e239'  1           0

The double negative expression looks confusing. Simply put, the condition below will return TRUE if all the character in the expression is a digit from 0 through 9.

[CharExpression] NOT LIKE '%[^0-9]%' 

Be careful, this regular expression only checks for digits. So if the input has a decimal point, it will still return FALSE.

UPDATE:

Starting SQL 2012, a new function is provided for checking if a value may be converted to a specific data type. TRY_CONVERT() is a function that converts a value to a specific data type and returns a NULL value if the value cannot be converted to the expected data type.

Here's a sample:

select ValueToBeConverted = '1e35', IsNumericFunction = isnumeric(1e35), 
       ConvertedValue = try_convert(int, 1e35), ConvertedValue = try_convert(float, 1e35)

Here's the resultset:

ValueToBeConverted IsNumericFunction ConvertedToInt ConvertedToFloat
------------------ ----------------- -------------- ----------------------
1e35               1                 NULL           1E+35

Notice that the fourth column returned a valid value as the letter "e" is a part of numeric value.

As always, comments are welcome...


~~ CK