In SQL Server, you can use the ISNUMERIC() function to find out whether an expression is numeric or not.

The function returns 1 if the expression is numeric, and 0 if it’s not.

To use this function, simply pass the value/expression to the function while calling it.

Example 1 – Numeric Expression

Here’s an example to demonstrate what happens when you pass a numeric expression to this function.

SELECT ISNUMERIC(250) AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

In this case, the value is numeric and the result is 1.

We get the same result even if the value is provided as a string (enclosed in single quotes).

SELECT ISNUMERIC('250') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

Example 2 – Non-Numeric Expression

Here’s what happens when the value is not numeric.

SELECT ISNUMERIC('Hey!') AS Result;

Result:

+----------+
| Result   |
|----------|
| 0        |
+----------+

Example 3 – A Database Example

Here’s an example of using ISNUMERIC() in a WHERE clause when querying a database:

USE WideWorldImportersDW;
SELECT COUNT(*) AS [Count]
FROM Dimension.Customer
WHERE ISNUMERIC([Postal Code]) = 1;

Result:

+---------+
| Count   |
|---------|
| 402     |
+---------+

This returns the count of all rows with a numeric postal code.

Unexpected Results? When Non-Numeric IS Numeric

Some characters are treated as being numeric, even when they’re not a number. This is something you need to be aware of when using this function, otherwise you could get results that you don’t expect.

See Non-Number Characters that Return Positive when using ISNUMERIC() for an explanation and examples.