PostgreSQL: How to Find Position of Character in String

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

You can use the following syntax to do so:

SELECT *,
       POSITION('Guard' IN role)
FROM athletes;

This particular example will find the starting position of the substring ‘Guard’ in the text column named role of the table named athletes.

Note: If the substring ‘Guard’ does not exist in a string then a value of 0 will be returned.

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

Example: How to Find Position of Character in 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 PRIMARY KEY,
  team TEXT,
  role TEXT
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 'Shooting Guard');
INSERT INTO athletes VALUES (0002, 'Hawks', 'Point Guard');
INSERT INTO athletes VALUES (0003, 'Rockets', 'Center');
INSERT INTO athletes VALUES (0004, 'Spurs', 'Point Guard');
INSERT INTO athletes VALUES (0005, 'Kings', 'Power Forward');
INSERT INTO athletes VALUES (0006, 'Celtics', 'Shooting Guard');
INSERT INTO athletes VALUES (0007, 'Lakers', 'Point Guard');

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

 id |  team   |      role      
----+---------+----------------
  1 | Mavs    | Shooting Guard
  2 | Hawks   | Point Guard
  3 | Rockets | Center
  4 | Spurs   | Point Guard
  5 | Kings   | Power Forward
  6 | Celtics | Shooting Guard
  7 | Lakers  | Point Guard

Suppose that we would like to find the position of the substring ‘Guard’ in each string of the role column.

We can use the following syntax to do so:

SELECT *,
       POSITION('Guard' IN role)
FROM athletes;

 id |  team   |      role      | position 
----+---------+----------------+----------
  1 | Mavs    | Shooting Guard |       10
  2 | Hawks   | Point Guard    |        7
  3 | Rockets | Center         |        0
  4 | Spurs   | Point Guard    |        7
  5 | Kings   | Power Forward  |        0
  6 | Celtics | Shooting Guard |       10
  7 | Lakers  | Point Guard    |        7

Notice that the resulting table contains a new column named position that contains the starting position of the substring ‘Guard’ within the string in the role column.

For example, we can see:

• The string ‘Guard’ appears in position 10 of the role column for the first player.
• The string ‘Guard’ appears in position 7 of the role column for the second player.
• The string ‘Guard’ appears in position 0 of the role column for the third player, i.e. the string ‘Guard’ does not exist anywhere in the string.

And so on.

Feel free to change ‘Guard’ to any substring or character that you would like to search for in your own text column.

Note: When using the POSITION statement to find the starting position of a substring, spaces count as characters within strings.

Additional Resources

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

PostgreSQL: Extract Substring Before Specific Character
PostgreSQL: Extract Substring After Specific Character
PostgreSQL: How to Extract Substring Between Two Characters