Often in PostgreSQL you may want to check if a string ends with a specific value.
You can use the LIKE function with the following syntax to do so:
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 ends with the string avs.
The following example shows how to use each method in practice.
Related: Check if String Starts with Specific Value in PostgreSQL
Example: How to Check if String Ends with Specific Value 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, 'Bucks', 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, 'Bulls', 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 | Bucks | 12 | 3
4 | Kings | 25 | 9
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
7 | Bulls | 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 ends with 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 ends with the substring avs.
In particular, we can see that the team names Mavs and Cavs were included since both of these team names end with avs.
Feel free to use similar syntax to select rows that end with a specific string in a column of your own table.
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 Check if String Contains Substring
PostgreSQL: How to Check if String is a Number
