SQL ISNUMERIC Function (Transact SQL)

By: Kris Wenzel   |   Updated: March 6, 2022  
Works With: 

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

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