SQL Server ISNUMERIC() Function

The SQL Server (Transact-SQL) ISNUMERIC() function is used to test whether an expression is a valid numeric type. The function returns 1 if the expression is a valid numeric type, else returns 0.

Syntax

ISNUMERIC(expression)

Parameters

Return Value

Returns 1 if the expression is a valid numeric type. Returns 0 otherwise.

Example 1:

The example below shows the usage of ISNUMERIC() function.

SELECT ISNUMERIC('Paris');
Result: 0

SELECT ISNUMERIC(NULL);
Result: 0

SELECT ISNUMERIC(0);
Result: 1

SELECT ISNUMERIC(10);
Result: 1

SELECT ISNUMERIC(10.25);
Result: 1

SELECT ISNUMERIC('10.25');
Result: 1

Example 2:

Consider a database table called Product with the following records:

If the OrderQuantity is optional and can contain NULL values. The statement mentioned below will give NULL value.

SELECT *, Price * (StockQuantity + OrderQuantity) AS Inventory  
FROM Product;

This will produce the result as shown below:

To avoid such situations, the ISNUMERIC() function can be used in conjunction with IIF() function to provide an alternative value to the column if it contains NULL value.

SELECT *, 
Price * (StockQuantity + IIF(ISNUMERIC(OrderQuantity)=0, 0, OrderQuantity)) AS Inventory
FROM Product;

This will produce the result as shown below: