The following example returns all rows that don’t contain any numbers in PostgreSQL.

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

Example

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

SELECT ProductName 
FROM Products;

Result:

           productname           
---------------------------------
 Left handed screwdriver
 Right handed screwdriver
 Long Weight (blue)
 Long Weight (green)
 Straw Dog Box
 Bottomless Coffee Mugs (4 Pack)
 Smash 2000 Sledge Hammer
 Chainsaw (3 spare fingers)

This column contains character data, but some rows contain numbers within that character data (even though they’re not stored as a numeric type).

We can use the following query to return just those rows that don’t contain any numerical digits:

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

Result:

       productname        
--------------------------
 Left handed screwdriver
 Right handed screwdriver
 Long Weight (blue)
 Long Weight (green)
 Straw Dog Box
(5 rows)

As expected, only those rows that don’t contain any numerical digits are returned.

In PostgreSQL, !~ is a case-sensitive operator that is used for returning values that don’t match the given regular expression. For case-insensitive matches, use !~*.

You can use ~ to return all rows that do match the regular expression (and ~* for case-insensitive matches).