The SQL ISNUMERIC function checks the input expression for a valid numeric data type. It returns 1 if the input value is numeric and 0 if it is not.

Description

The ISNUMERIC function in SQL tells if the input expression can be converted to a numeric value or not. It returns an integer as output and does not tell about the actual data type.

The valid numeric data types in SQL are bigint, int, smallint, tinyint, and bit for exact numeric values, decimal and numeric for fixed precision values, float and real for approximated precision values, and money and smallmoney for currency values.

SQL ISNUMERIC Usage Notes

The only argument for the ISNUMERIC function is the expression to be evaluated for a numeric data type. If it is a valid numeric value, ISNUMERIC returns 1, otherwise 0. ISNUMERIC also returns 1 for some symbols like plus, minus, currency’s dollar sign, etc.

The return type is int for 0 or 1.

Syntax

ISNUMERIC (input_expression)

SQL ISNUMERIC Examples

The following example shows how to use the ISNUMERIC function for positive and negative integers with and without symbols.

SELECT ISNUMERIC('90') A, ISNUMERIC('-90') B, ISNUMERIC('$90') C, ISNUMERIC('A+') D;
/* Answer */
SELECT ISNUMERIC('90') A, ISNUMERIC('-90') B, ISNUMERIC('$90') C, ISNUMERIC('A+') D;

The query returns 1 for the first three numeric expressions and 0 for the last one as it does not evaluate to a numeric value.

ISNUMERIC function filters out numeric values in a database table.

The following example uses the ISNUMERIC function on the AdventureWorks2019 database.

SELECT AccountNumber, ISNUMERIC(AccountNumber) IsNumericAccount FROM Purchasing.Vendor
/* Answer */
SELECT AccountNumber, ISNUMERIC(AccountNumber) IsNumericAccount
FROM Purchasing.Vendor

The ISNUMERIC function evaluates all account numbers for a numeric expression. It returns 0 if the account number is not a numeric value.

The ISNUMERIC function is commonly used with a CASE statement. We can perform a different function in case the value is numeric. The following example explains this.

SELECT Name, AccountNumber, CASE WHEN ISNUMERIC(AccountNumber) <> 1 THEN 'Y' ELSE 'N' END NonNumericAccountNumber FROM Purchasing.Vendor
/* Answer */
SELECT Name,
   AccountNumber,
   CASE WHEN ISNUMERIC(AccountNumber) <> 1 THEN 'Y'
        ELSE 'N' 
   END NonNumericAccountNumber
FROM Purchasing.Vendor

The above query returns Y for a numeric account number of a vendor. It returns N if the account number is not a numeric value.

See Also

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>