Often in PostgreSQL you may want to check if a string starts with a specific value.
You can use the starts_with() function with the following syntax to do so:
SELECT *
FROM athletes
WHERE starts_with(team, 'Bu');
This particular example will select all rows where the string in the team column of the athletes table starts with Bu.
The following example shows how to use each method in practice.
Example: How to Check if String Starts 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, 'Bulls', 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 | Bulls | 20 | 8
10 | Hawks | 26 | 3
We can use the following syntax to only select the rows where the string in the team column starts with the substring Bu:
SELECT *
FROM athletes
WHERE starts_with(team, 'Bu');
athleteid | team | points | assists
-----------+-------+--------+---------
3 | Bucks | 12 | 3
7 | Bulls | 10 | 5
9 | Bulls | 20 | 8
Notice that this returns all rows where the string in the team column starts with the substring Bu.
In particular, we can see that the team names Bucks and Bulls were returned since both of these team names start with Bu.
Feel free to use similar syntax to select rows that start 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
