Often in PostgreSQL you may want to check if a string contains a specific substring.
You can use the following methods to do so:
Method 1: Check if String Contains Substring
SELECT *
FROM athletes
WHERE team LIKE '%avs%';
This particular example will select all rows where the string in the team column of the athletes table contains the substring avs.
Method 2: Check if String Contains Any of Several Substrings
SELECT *
FROM athletes
WHERE team LIKE ANY ('{%avs%,%ings%}');
This particular example will select all rows where the string in the team column of the athletes table contains the substring avs or the substring ings.
Note that both methods use the LIKE command to match a specific substring, using % wildcard operators before and after the substring to indicate that any characters can come before or after the substring as well.
The following example shows how to use each method in practice.
Example: How to Check if String Contains Substring 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 (
athleteID INTEGER PRIMARY KEY,
team TEXT,
points INTEGER,
assists INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Celtics', 10, 5);
INSERT INTO athletes VALUES (0008, 'Celtics', 14, 13);
INSERT INTO athletes VALUES (0009, 'Cavs', 20, 8);
INSERT INTO athletes VALUES (0010, 'Hawks', 26, 3);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points | assists
-----------+---------+--------+---------
1 | Mavs | 22 | 5
2 | Hawks | 30 | 12
3 | Kings | 12 | 3
4 | Kings | 25 | 9
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
7 | Celtics | 10 | 5
8 | Celtics | 14 | 13
9 | Cavs | 20 | 8
10 | Hawks | 26 | 3
We can use the following syntax to only select the rows where the string in the team column contains the substring avs:
SELECT *
FROM athletes
WHERE team LIKE '%avs%';
athleteid | team | points | assists
-----------+------+--------+---------
1 | Mavs | 22 | 5
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
9 | Cavs | 20 | 8
Notice that this returns all rows where the string in the team column contains the substring avs.
We could also use the following syntax to instead select all rows where the string in the team column contains the substring avs or ings:
SELECT *
FROM athletes
WHERE team LIKE ANY ('{%avs%,%ings%}');
athleteid | team | points | assists
-----------+-------+--------+---------
1 | Mavs | 22 | 5
3 | Kings | 12 | 3
4 | Kings | 25 | 9
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
9 | Cavs | 20 | 8
This returns all rows where the string in the team column contains the substring avs or ings.
Note: Feel free to include as many substrings as you would like in the array specified after the ANY statement.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Extract Numbers from a String
PostgreSQL: How to Pad Numbers with Leading Zeros
PostgreSQL: How to Extract Substring Before Specific Character
