PostgreSQL: How to Get Last Word from a String

Often in PostgreSQL you may want to get the last word from a string.

You can use the split_part() function with the following syntax to do so:

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

This particular example gets the last word from the player_info column of the athletes table and saves it to a new column named player_position.

Note that we use the split_part() function to get the last word, which uses the following syntax:

split_part(string, delimiter, position)

where:

• string: Name of the string column
• delimiter: Delimiter to use to split elements
• position: The element to extract after the split (-1 = last element)

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

Example: How to Get Last Word from a String 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 (
  id INTEGER,
  team TEXT,
  player_info TEXT
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 'The Starting Guard');
INSERT INTO athletes VALUES (0002, 'Hawks', 'The Backup Guard');
INSERT INTO athletes VALUES (0003, 'Kings', 'The Starting Forward');
INSERT INTO athletes VALUES (0004, 'Lakers', 'The Backup Forward');
INSERT INTO athletes VALUES (0005, 'Celtics', 'The Starting Center');
INSERT INTO athletes VALUES (0006, 'Nuggets', 'The Backup Center');

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

 id |  team   |     player_info      
----+---------+----------------------
  1 | Mavs    | The Starting Guard
  2 | Hawks   | The Backup Guard
  3 | Kings   | The Starting Forward
  4 | Lakers  | The Backup Forward
  5 | Celtics | The Starting Center
  6 | Nuggets | The Backup Center

Suppose that we would like to get the last word from each string in the player_info column.

We can use the following syntax to do so:

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

 id |  team   |     player_info      | player_position 
----+---------+----------------------+-----------------
  1 | Mavs    | The Starting Guard   | Guard
  2 | Hawks   | The Backup Guard     | Guard
  3 | Kings   | The Starting Forward | Forward
  4 | Lakers  | The Backup Forward   | Forward
  5 | Celtics | The Starting Center  | Center
  6 | Nuggets | The Backup Center    | Center

The resulting table contains a new column named player_position that contains the last word from each string in the player_info column.

You can use similar syntax with the split_part() function in PostgreSQL to get the last word from a string column in your own table.

Additional Resources

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

PostgreSQL: How to Split One Column into Multiple Columns
PostgreSQL: How to Use string_agg with Distinct Values
PostgreSQL: How to Use string_agg with Order By