If you have a column in a database table that contains character data, but some rows also contain numbers, you can use the following SQL queries to return just those rows that don’t contain numbers within the value.

Strictly speaking, numbers can be represented by numerical digits, words and other symbols, but for the purpose of this article, “number” means “numerical digit”. So we’re finding values that don’t contain any numerical digits.

The query you use will depend on your DBMS.

SQL Server

In SQL Server, we can use a query like this:

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

Here, we’re returning all rows where the ProductName column does not contain any numerical digits.

Oracle

In Oracle, we can use the REGEXP_LIKE() function:

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

In this case we provide a regular expression pattern that matches the values that we don’t want to be returned. The values are in fact matched, but we also negate the match by using NOT, which means that everything that does not match is returned.

Another way to do it is to use the [:digit:] POSIX character class:

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

MySQL

In MySQL, we can use the NOT REGEX function:

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

Another way to write it is like this:

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

Or we can use the [:digit:] POSIX character class:

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

MariaDB

In MariaDB, we can use its implementation of the NOT REGEX function:

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

Another way to write it is like this:

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

Or we can use the [:digit:] POSIX character class:

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

PostgreSQL

Here’s how we can do it in Postgres:

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

SQLite

In SQLite, we can do this:

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

In SQLite, the REGEXP operator is a special syntax for the REGEXP() user function, so we can also do the following:

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