The ISNUMERIC() function in SQL Server enables you to check whether or not an expression is numeric.

However, there may be times where you get results that you didn’t expect. This could happen if you have an expression that contains a character that is not a number, but is still accepted by ISNUMERIC() as being numeric.

There are a bunch of characters that ISNUMERIC() accepts as numeric that you might not have thought of as numeric. These include characters such as plus (+), minus (-), and the various currency symbols. Also, depending on its placement, the letter e could also allow the whole expression to be interpreted as numeric.

Examples

Here are some examples of what I mean:

SELECT 
  ISNUMERIC('+') AS [+],
  ISNUMERIC('-') AS [-],
  ISNUMERIC('$') AS [$],
  ISNUMERIC('1e2') AS [1e2],
  ISNUMERIC('1e+2') AS [1e+2],
  ISNUMERIC('e') AS [e],
  ISNUMERIC('e+') AS [e+];

Result:

+-----+-----+-----+-------+--------+-----+------+
| +   | -   | $   | 1e2   | 1e+2   | e   | e+   |
|-----+-----+-----+-------+--------+-----+------|
| 1   | 1   | 1   | 1     | 1      | 0   | 0    |
+-----+-----+-----+-------+--------+-----+------+

Note that the e and e+ return a negative result when they’re on their own, but return a positive result when surrounded by numbers.

This is probably because, on their own, they don’t represent a number, but when surrounded by numbers, the whole expression could be interpreted as scientific notation (e and e+ are often used in scientific notation).

Numeric Data Types

According to the Microsoft documentation, data types that ISNUMERIC() will recognise as numeric include the following.

Exact Numerics

• bigint
• int
• smallint
• tinyint
• bit

Fixed Precision

• decimal
• numeric

Approximate

• float
• real

Monetary Values

• money
• smallmoney

In addition, ISNUMERIC() returns 1 for some characters that are not numbers (as seen in the above example). This includes characters such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).

Also, as seen in the example, the letter e can affect the result when its part of a larger expression, and depending on its placement in that expression.