In SQL Server, we can use the ISNUMERIC() function to return numeric values from a column.

We can alternatively run a separate query to return all values that contain numeric data.

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

Seeing as this is a varchar column, it contains character data. It can contain numeric data, but it’s not stored as a numeric type. Therefore, the column can contain both textual data and numeric. The following examples check this column for numeric values.

The ISNUMERIC() Function

SELECT c1
FROM t1
WHERE ISNUMERIC(c1) = 1;

Result:

+--------+
| c1     |
|--------|
| 0      |
| 1      |
| +1     |
| -1     |
| +1     |
| 00.00  |
| 73.45  |
| +73.45 |
| -73.45 |
| .246   |
| -.34e7 |
| 12.e-3 |
| 1.2e+4 |
+--------+

Here, I used the ISNUMERIC() function along with the Equal To (=) operator to return the values that are numeric. The function returns 1 when it’s numeric and 0 when it’s not.

Find Values that Contain Numbers

We can use the following query to return all rows that contain numeric values (even if they also contain other characters).

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

Result:

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

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