PostgreSQL: Find Last Occurrence of Character in String

Often in PostgreSQL you may want to find the position of the last occurrence of a character in a string.

You can use the following syntax to do so:

SELECT
*,
length(team) - position('a' in reverse(team)) + 1 AS last_occurence
FROM athletes;

This particular example will create a new column named last_occurrence that contains the position of the last occurrence of the character ‘a’ in the string in the team column of the athletes table.

Here is how this syntax works:

• First, we use the reverse() function to reverse the string in the team column.
• Then, we use the position() function to find the first occurrence of ‘a’ in the reversed string.
• We then subtract this value from the total length of the string in the team column.
• Lastly, we add 1. This will return the correct position of the last occurrence of ‘a’ in the original string.

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

Example: How to Find Last Occurrence of Character in String 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,
  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 find the last occurrence of the character ‘a’ in each string in the team column.

We can use the following syntax to do so:

SELECT
*,
length(team) - position('a' in reverse(team)) + 1 AS last_occurence
FROM athletes;

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

Notice that the resulting table contains a new column named last_occurrence that contains the position of the last occurrence of the character ‘a’ in the team column.

You can use similar syntax to find the last occurrence of a specific character in a string column of your own table.

Additional Resources

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

PostgreSQL: Remove First and Last Character from String
PostgreSQL: How to Remove Special Characters from String
PostgreSQL: How to Select Last N Characters in a String
PostgreSQL: How to Select First N Characters in a String