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.