PostgreSQL: Extract Substring Before Specific Character

Often in PostgreSQL you may want to extract a substring before a specific character.

You can use the following syntax to do so:

SELECT split_part(player_info, ',', 1) AS player_position
FROM athletes;

This particular example will extract the substring before the comma in each string in the player_info column of the table named athletes.

The resulting substring will be saved to a new column named player_position.

Note that we use the split_part() function to split the string in the player_info column into fields based on where the comma occurs, then we specify the number 1 to extract the 1st field that results from the split.

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

Example: Extract Substring Before Character in PostgreSQL

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

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

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Guard, Mavericks');
INSERT INTO athletes VALUES (0002, 'Forward, Spurs');
INSERT INTO athletes VALUES (0003, 'Guard, Lakers');
INSERT INTO athletes VALUES (0004, 'Center, Rockets');
INSERT INTO athletes VALUES (0005, 'Forward, Celtics');
INSERT INTO athletes VALUES (0006, 'Forward, Magic');
INSERT INTO athletes VALUES (0007, 'Guard, Blazers');

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

 id |   player_info    
----+------------------
  1 | Guard, Mavericks
  2 | Forward, Spurs
  3 | Guard, Lakers
  4 | Center, Rockets
  5 | Forward, Celtics
  6 | Forward, Magic
  7 | Guard, Blazers

Suppose that we would like to extract the substring before the comma in the player_info column.

We can use the following syntax to do so:

SELECT id,
       split_part(player_info, ',', 1) AS player_position
FROM athletes;

 id | player_position 
----+-----------------
  1 | Guard
  2 | Forward
  3 | Guard
  4 | Center
  5 | Forward
  6 | Forward
  7 | Guard

Notice that the resulting table contains a new column named player_position that contains the substring before the comma from each string in the original player_info column.

Feel free to use similar syntax to extract a substring before a specific character in your own table.

Additional Resources

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

PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to Extract Substring Between Two Characters
PostgreSQL: How to Calculate Difference Between Rows