PostgreSQL: Extract Substring After Specific Character

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

You can use the following syntax to do so:

SELECT split_part(player_info, ':', 2) AS team
FROM athletes;

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

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

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

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

Example: Extract Substring After 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, 'team:Mavericks');
INSERT INTO athletes VALUES (0002, 'team:Spurs');
INSERT INTO athletes VALUES (0003, 'team:Lakers');
INSERT INTO athletes VALUES (0004, 'team:Rockets');
INSERT INTO athletes VALUES (0005, 'team:Celtics');
INSERT INTO athletes VALUES (0006, 'team:Magic');
INSERT INTO athletes VALUES (0007, 'team:Blazers');

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

 id |  player_info   
----+----------------
  1 | team:Mavericks
  2 | team:Spurs
  3 | team:Lakers
  4 | team:Rockets
  5 | team:Celtics
  6 | team:Magic
  7 | team:Blazers

Suppose that we would like to extract the substring after the colon in the player_info column.

We can use the following syntax to do so:

SELECT id,
       split_part(player_info, ':', 2) AS team
FROM athletes;

 id |   team    
----+-----------
  1 | Mavericks
  2 | Spurs
  3 | Lakers
  4 | Rockets
  5 | Celtics
  6 | Magic
  7 | Blazers

Notice that the resulting table contains a new column named team that contains the substring after the colon from each string in the original player_info column.

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

Additional Resources

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

PostgreSQL: How to Extract Substring Before Specific Character
PostgreSQL: How to Extract Substring Between Two Characters
PostgreSQL: How to Extract Date from a Timestamp