The following example returns all rows that contain at least one numerical digit in Oracle Database.

Sample Data

Suppose we have a table called Products with the following data in its ProductName column:

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

Example Query

We can use the following query to return just those rows that contain numbers:

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

Result:

Bottomless Coffee Cup (4 pack)
Urban Dictionary Version 2.3
10 Songs

Only those rows that contain numbers are returned. By “numbers”, I mean numerical digits. Numbers can also be represented by words or symbols, but this example only detects numerical digits.

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

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

Result:

Bottomless Coffee Cup (4 pack)
Urban Dictionary Version 2.3
10 Songs