If you ever encounter a character column that should be numeric, there’s always a possibility that it contains non-numeric data that you don’t know about.

For example, someone might have set up a Price column as a varchar column that should have been a numeric column, and now you need to clean up after them. You might start by identifying all non-numeric data so that you can work out what to do with it before converting the column to a numeric type.

In SQL, you can run a query to return non-numeric data from the column. The query you use will largely depend on your DBMS.

SQL Server

SQL Server has an ISNUMERIC() function that returns 1 for numeric values and 0 for non-numeric values.

Here’s an example of using this function to return just the non-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 not 1.

Another way to do it would be to use = 0 instead of <> 1.

Oracle

In Oracle, we can negate the REGEXP_LIKE() function with a regular expression pattern:

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

The regex pattern matches floating point numbers, and so we negated it with NOT.

Matching non-integers can be done with a less complex pattern:

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

MySQL

In MySQL, we can negate the REGEXP operator:

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

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

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

MariaDB

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

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

And for non-integers:

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

PostgreSQL

Here’s the Postgres version:

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

And for non-integers:

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), then negate that:

SELECT c1
FROM t1
WHERE typeof(c1) <> 'integer'
AND typeof(c1) <> 'real';

In this case we can remove the AND operator to limit it to just non-integers.

Alternatively, we could use typeof() to check for only types of null, text, or blob.

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

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

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