ISNUMERIC

The ISNUMERIC function in SQL Server is a built-in function that is used to determine whether an expression can be evaluated as a numeric data type. It returns 1 if the expression can be converted to a numeric type; otherwise, it returns 0. The ISNUMERIC function is often employed in scenarios where you need to validate whether a given value is numeric before performing numeric operations on it.

Syntax

Here is the basic syntax of the ISNUMERIC function:

ISNUMERIC ( expression )

The expression parameter represents the value or column that you want to check for numeric compatibility.

Example

Here are some examples of using the ISNUMERIC function:

Checking if a literal value is numeric:

SELECT ISNUMERIC('12345'); -- Returns 1
SELECT ISNUMERIC('abc');   -- Returns 0

Checking if a column in a table is numeric:

CREATE TABLE SampleTable (
    ID INT PRIMARY KEY,
    ValueString VARCHAR(50)
);

INSERT INTO SampleTable VALUES (1, '123');
INSERT INTO SampleTable VALUES (2, 'abc');

SELECT ID, ValueString, ISNUMERIC(ValueString) AS IsNumeric
FROM SampleTable;

The result would be:

| ID | ValueString | IsNumeric |
|----|-------------|-----------|
| 1  | 123         | 1         |
| 2  | abc         | 0         |

It’s important to note that ISNUMERIC is not foolproof. While it checks whether a value can be converted to a numeric type, it does not differentiate between different numeric types (e.g., integer, decimal, float). Additionally, it may return 1 for values that are technically numeric but not suitable for certain numeric operations.

In scenarios where you need more precise validation or want to handle specific numeric types, you may need to use other functions or a combination of functions tailored to your requirements.