PostgreSQL: Select Rows where Column NOT LIKE Multiple Values

Often in PostgreSQL you may want to select rows where a specific column is NOT LIKE multiple values.

You can use the following syntax to do so:

SELECT *
FROM athletes
WHERE role NOT LIKE ALL(ARRAY['Pow%', 'Shoo%']);

This particular example will select all rows from the table named athletes where the string in the role column is not like any of the following patterns:

• Pow
• Shoo

By using the NOT LIKE  statement with the ALL statement, we are able to select strings that do not match all of the patterns specified in the ARRAY list.

The following example shows how to use this syntax in practice.

Example: Select Rows where Column NOT LIKE Multiple Values in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  team TEXT,
  role TEXT
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 'Shooting Guard');
INSERT INTO athletes VALUES (0002, 'Hawks', 'Point Guard');
INSERT INTO athletes VALUES (0003, 'Rockets', 'Center');
INSERT INTO athletes VALUES (0004, 'Spurs', 'Point Guard');
INSERT INTO athletes VALUES (0005, 'Kings', 'Power Forward');
INSERT INTO athletes VALUES (0006, 'Celtics', 'Shooting Guard');
INSERT INTO athletes VALUES (0007, 'Lakers', 'Center');

-- view all rows in table
SELECT * FROM athletes;

 id |  team   |      role      
----+---------+----------------
  1 | Mavs    | Shooting Guard
  2 | Hawks   | Point Guard
  3 | Rockets | Center
  4 | Spurs   | Point Guard
  5 | Kings   | Power Forward
  6 | Celtics | Shooting Guard
  7 | Lakers  | Center

Suppose that we would like to only select the rows where the string in the role column does not match any of the following patterns:

• Pow
• Shoo

We can use the following syntax to do so:

SELECT *
FROM athletes
WHERE role NOT LIKE ALL(ARRAY['Pow%', 'Shoo%']);

 id |  team   |    role     
----+---------+-------------
  2 | Hawks   | Point Guard
  3 | Rockets | Center
  4 | Spurs   | Point Guard
  7 | Lakers  | Center

The resulting table contains only the rows for players whose string in the role column does not match any of the patterns that we specified in the ARRAY list.

Note: In this particular example we included two patterns in the ARRAY list but you can include as many patterns as you would like.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Extract Numbers from a String
PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: Count Number of Occurrences of Character in String