PostgreSQL: How to Use LIKE Operator with Multiple Values

Often in PostgreSQL you may want to use the LIKE operator with multiple values to select rows in a table that match one of several patterns.

You can use the following syntax to do so:

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

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

• Poi
• Pow
• Shoo

By using the LIKE statement with the ANY statement, we are able to select strings that match any of the patterns specified in the ARRAY list.

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

Example: How to Use LIKE Operator with 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 starts with one of the following patterns:

• Poi
• Pow
• Shoo

We can use the following syntax to do so:

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

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

The resulting table contains only the rows for players whose string in the role column matches one of the patterns that we specified in the ARRAY list.

Feel free to use similar syntax with the LIKE statement to return rows from a table that match any number of patterns that you’d 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