The following examples return only those rows that don’t contain any numeric digits in a given column in Oracle Database.

Sample Data

Suppose we have a table with the following data:

SELECT ProductName 
FROM Products;

Result:

Left Handed Screwdriver
Right Handed Screwdriver
Bottomless Coffee Cup (4 pack)
Urban Dictionary Version 2.3
Beer Water
10 Songs

The ProductName table uses a varchar2 data type, and so it contains character data. But it can also contain numeric digits.

Example 1 – Regex

Here’s a query we can use to return all rows that don’t contain numeric digits:

SELECT ProductName
FROM Products 
WHERE NOT REGEXP_LIKE(ProductName, '[0-9]+');

Result:

Left Handed Screwdriver
Right Handed Screwdriver
Beer Water

Example 2 – POSIX

In Oracle Database, the REGEXP_LIKE condition complies with the POSIX regular expression standard. Therefore, we can get the same result with the following query:

SELECT ProductName
FROM Products 
WHERE NOT REGEXP_LIKE(ProductName, '[[:digit:]]');

Result:

Left Handed Screwdriver
Right Handed Screwdriver
Beer Water