PostgreSQL: Extract Substring Between Two Characters

Often in PostgreSQL you may want to extract a substring between two characters.

You can use the following syntax to do so:

SELECT substring(team FROM 'The (.+) are') AS team_name
FROM athletes;

This particular example will extract the substring between the patterns The and are from each string in the team column of the table named athletes and save the substring to a new column named team_name.

Note that we use (.+) to match all characters between The and are in the string.

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

Example: Extract Substring Between Two Characters 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,
  team TEXT
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'The Mavs are from Dallas');
INSERT INTO athletes VALUES (0002, 'The Nets are from Brooklyn');
INSERT INTO athletes VALUES (0003, 'The Rockets are from Houston');
INSERT INTO athletes VALUES (0004, 'The Kings are from Sacramento');
INSERT INTO athletes VALUES (0005, 'The Celtics are from Boston');

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

 id |             team              
----+-------------------------------
  1 | The Mavs are from Dallas
  2 | The Nets are from Brooklyn
  3 | The Rockets are from Houston
  4 | The Kings are from Sacramento
  5 | The Celtics are from Boston

Suppose that we would like to extract the team name from each string in the team column by extracting the substring between the patterns The and are.

We can use the following syntax to do so:

SELECT id,
       substring(team FROM 'The (.+) are') AS team_name
FROM athletes;

 id | team_name 
----+-----------
  1 | Mavs
  2 | Nets
  3 | Rockets
  4 | Kings
  5 | Celtics

Notice that the resulting table contains a new column named team_name that contains the substring between the patterns The and are from each string in the team column.

Feel free to use similar syntax to extract a substring between two characters 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 INSERT If Not Exists Already
PostgreSQL: How to Calculate Difference Between Rows