database thumb

How to Find All Non Numeric Values in a Column in SQL?

If we encounter a character column that should be numeric but it is not then it is always possible that it may contains non-numeric data that is junk or we don’t have any idea what it is. Let’s suppose, someone might have set up a Price column as a varchar column that should have been a numeric column at first place, and now we need to deal with that mistake. We might start by identifying all non-numeric data so that we can work out what to do with it before converting the column to a numeric type.

SQL Server

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

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

Now we are can also use “=0” instead of “<>1”.

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

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]+)?$');

In the query above we are dealing with values which are not real numbers. We can deal with not integer values with much more ease.

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

MySQL And MariaDB

In MySQL, we can use the REGEXP operator.

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

Again we are dealing with NOT real numbers here. Dealing with NOT integers will be easier.

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

PostgreSQL

Here’s the Postgres query for NOT real numbers.

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

and here is the query for not integer numbers.

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

SQLite

In SQLite we have the luxury to use typeof()function. Here is an example.

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

But if we prefer REGEXPthen here is the query for that.

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

I hope this will help you to filter out non numeric values out of table and deal with them. If you have any questions, please use comments section.