SQL Server ISNUMERIC Function

Summary: in this tutorial, you will learn how to use the SQL Server ISNUMERIC() function to check if a value is a valid numeric type.

Introduction to SQL Server ISNUMERIC() function

The ISNUMERIC() accepts an expression and returns 1 if the expression is a valid numeric type; otherwise, it returns 0.

The following shows the syntax of the ISNUMERIC() function:

ISNUMERIC ( expression )  
Code language: SQL (Structured Query Language) (sql)

In this syntax, the expression is any valid expression to be evaluated.

Note that a valid numeric type is one of the following:

• Exact numbers: BIGINT, INT, SMALLINT, TINYINT, and BIT
• Fixed precision: DECIMAL, NUMERIC
• Approximate: FLOAT, REAL
• Monetary values: MONEY, SMALLMONEY

The ISNUMERIC() actually checks if a value can be converted to a numeric data type and returns the right answer. However, it doesn’t tell you which datatype and properly handle the overflow.

This was why the TRY_CAST(), TRY_PARSE(), and TRY_CONVERT() function was introduced since SQL Server 2012.

SQL Server ISNUMERIC() examples

Let’s take some examples o fusing the ISNUMERIC() function.

This example uses the ISNUMERIC() function to check if the string '$10' can be converted to a number or not:

SELECT 
    ISNUMERIC('$10') result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
-----------
1

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

The following example checks whether the '-2.23E-308' string is a number:

SELECT 
    ISNUMERIC('-2.23E-308') result;
Code language: SQL (Structured Query Language) (sql)

The output is:

result
-----------
1

(1 row affected)        
Code language: SQL (Structured Query Language) (sql)

The following example returns 0 indicating that the string '+ABC' is not a number:

SELECT 
    ISNUMERIC('+ABC') result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
-----------
0

(1 row affected)        
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server ISNUMERIC() to check if an expression is a valid numeric type.