If you ever need to use SQL to return just the numeric values in a character column, the method you use will largely depend on your DBMS. Most DBMSs support regular expressions (regex), but some don’t. And some include functions that can detect numeric values.

Here are examples of how to do it in the more popular DBMSs.

SQL Server

SQL Server has an ISNUMERIC() function that makes it easy for us.

Here’s an example of using this function to return just numeric values from a column:

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

Here, c1 is a varchar column (that may or may not contain numeric data) and t1 is the table.

In this case we used ISNUMERIC() in a WHERE clause to narrow the query results to just those where the result of ISNUMERIC(c1) is 1. This function returns either 1 (for numeric values) or 0 (for non-numeric values). We narrowed it to just numeric values.

Oracle

In Oracle, we can use the REGEXP_LIKE() function:

SELECT c1
FROM t1 
WHERE REGEXP_LIKE(c1, '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$');

The regex pattern matches floating point numbers.

Matching integers can be done with a less complex pattern:

SELECT c1
FROM t1 
WHERE REGEXP_LIKE(c1, '^[0-9]+$');

MySQL

In MySQL, we can use the REGEXP operator:

SELECT c1
FROM t1 
WHERE c1 REGEXP '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$';

For integers, we can use the same pattern that we used in the Oracle example for matching integers:

SELECT c1
FROM t1 
WHERE c1 REGEXP '^[0-9]+$';

MariaDB

In MariaDB, we can use its implementation of the REGEXP operator:

SELECT c1
FROM t1 
WHERE c1 REGEXP '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$';

And for integers:

SELECT c1
FROM t1 
WHERE c1 REGEXP '^[0-9]+$';

PostgreSQL

Here’s the Postgres version:

SELECT c1
FROM t1 
WHERE c1 ~ '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$';

And for integers only:

SELECT c1
FROM t1 
WHERE c1 ~ '^[0-9]+$';

SQLite

In SQLite, we have a couple of options. We can use the typeof() function to filter the results to just numeric types (real and integer):

SELECT c1
FROM t1
WHERE typeof(c1) = 'integer'
OR typeof(c1) = 'real';

In this case we can remove the OR operator to limit it to just integers.

If we prefer to use a regex pattern, we can apply that with the REGEXP function:

SELECT c1
FROM t1 
WHERE c1 REGEXP '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$';

For integers, we can apply the same pattern as with the previous examples for integers.

POSIX Character Classes

Oracle, MySQL, MariaDB, and Postgres support POSIX character classes. So we can use [:digit:] instead of [0-9] in our regular expressions.

Here’s an example for MariaDB/MySQL:

SELECT c1
FROM t1 
WHERE c1 REGEXP '^[[:digit:]]?$';

That’s the equivalent of the following:

SELECT c1
FROM t1 
WHERE c1 REGEXP '^[0-9]?$';