Sometimes you might need to search a database table for only those rows that contain at least one number in a given column.

Technically, numbers can be represented by words and other symbols, but here “number” means “numerical digit”.

Below are examples of how to find rows that contain at least one number in various SQL based DBMSs.

SQL Server

In SQL Server, we can use the LIKE operator:

SELECT ProductName 
FROM Products
WHERE ProductName LIKE '%[0-9]%';

That example returns the ProductName column from the Products table where there’s at least one digit in the ProductName column.

Oracle

In Oracle, we can use the REGEXP_LIKE condition with a regular expression pattern:

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

Oracle’s 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:]]');

MySQL

In MySQL, we can use the REGEXP function:

SELECT ProductName 
FROM Products
WHERE ProductName REGEXP '[0-9]+';

We can also use POSIX in MySQL:

SELECT ProductName 
FROM Products
WHERE ProductName REGEXP '[[:digit:]]';

MariaDB

MariaDB also has a REGEXP function, so we can use the same code as with MySQL:

SELECT ProductName 
FROM Products
WHERE ProductName REGEXP '[0-9]+';

We can also use POSIX in MariaDB:

SELECT ProductName 
FROM Products
WHERE ProductName REGEXP '[[:digit:]]';

PostgreSQL

Here’s PostgreSQL’s equivalent:

SELECT ProductName 
FROM Products
WHERE ProductName ~ '[0-9]+';

And the POSIX equivalent:

SELECT ProductName 
FROM Products
WHERE ProductName ~ '[[:digit:]]';

SQLite

In SQLite, we can use the following code:

SELECT ProductName 
FROM Products
WHERE ProductName REGEXP '[0-9]+';

The REGEXP operator is a special syntax for the REGEXP() user function in SQLite.

Therefore, we can use the following code to get the same result:

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