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.
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.
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.
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.
