There may be occasions where you need to check a column for non-numeric values. For example, you discover that a column is a varchar column when it really should be a numeric column.

This is easily done in SQL Server with the ISNUMERIC() function.

Sample Data

Suppose we create a table with a varchar column, and insert data as follows:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    c1 varchar(255)
);

INSERT INTO t1 (c1) VALUES 
    ('0'),
    ('1'),
    ('+1'),
    ('-1'),
    ('+1'),
    ('00.00'),
    ('73.45'),
    ('+73.45'),
    ('-73.45'),
    ('.246'),
    ('-.34e7'),
    ('12.e-3'),
    ('1.2e+4'),
    ('a'),
    ('9afc'),
    ('e7'),
    ('+e0'),
    ('Ten'),
    ('5 Dollars');

SELECT * FROM t1;

Result:

+-----------+
| c1        |
|-----------|
| 0         |
| 1         |
| +1        |
| -1        |
| +1        |
| 00.00     |
| 73.45     |
| +73.45    |
| -73.45    |
| .246      |
| -.34e7    |
| 12.e-3    |
| 1.2e+4    |
| a         |
| 9afc      |
| e7        |
| +e0       |
| Ten       |
| 5 Dollars |
+-----------+

Most of the above values are numeric, even though they’re in a varchar column. The following examples check this column for non-numeric values.

The ISNUMERIC() Function

SELECT c1
FROM t1
WHERE ISNUMERIC(c1) <> 1;

Result:

+-----------+
| c1        |
|-----------|
| a         |
| 9afc      |
| e7        |
| +e0       |
| Ten       |
| 5 Dollars |
+-----------+

Here, I used the ISNUMERIC() function along with the Not Equal To (<>) operator to check for values that are not numeric.

There may be a good reason for a column to be varchar instead of numeric. But if not, the values should be converted to their numeric equivalents, and then the column’s data type should be changed to a numeric type. This will help maintain the data integrity of the database.

Find Values that Don’t Contain Any Numbers

We can use the following query to return all rows that don’t contain numeric values.

SELECT c1
FROM t1 
WHERE c1 NOT LIKE '%[0-9]%';

Result:

+------+
| c1   |
|------|
| a    |
| Ten  |
+------+

This is a different result than the previous example, because we’re simply looking for all values that don’t contain any numeric data. In the previous example, we were looking for those values that are not numeric.