PostgreSQL: Count Number of Occurrences of Character in String

Often in PostgreSQL you may want to count the number of occurrences of a specific character in a string.

You can use the following syntax to do so:

SELECT (CHAR_LENGTH(team) - CHAR_LENGTH(REPLACE(team, 'g', ''))) 
       / CHAR_LENGTH('g') AS g_count
FROM athletes;

This particular example counts the number of occurrences of the character ‘g’ in the team column of the table named athletes.

Here is how this syntax works:

• First, we count the total characters in the team column.
• Then, we subtract the count of total characters in the team column after replacing each occurrence of ‘g’ with nothing.
• Then we divide by the length of the pattern we searched for, which was ‘g’ in this case.

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

Example: Count Number of Occurrences of Character in 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 (
  athleteID INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER,
  assists INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Celtics', 10, 5);
INSERT INTO athletes VALUES (0006, 'Nuggets', 14, 13);
INSERT INTO athletes VALUES (0007, 'Nuggets', 20, 8);
INSERT INTO athletes VALUES (0008, 'Hawks', 26, 3);

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

 athleteid |  team   | points | assists 
-----------+---------+--------+---------
         1 | Mavs    |     22 |       5
         2 | Hawks   |     30 |      12
         3 | Kings   |     12 |       3
         4 | Kings   |     25 |       9
         5 | Celtics |     10 |       5
         6 | Nuggets |     14 |      13
         7 | Nuggets |     20 |       8
         8 | Hawks   |     26 |       3

Suppose that we would like to count the number of occurrences of the character ‘g’ in each string in the team column.

We can use the following syntax to do so:

SELECT team,
       (CHAR_LENGTH(team) - CHAR_LENGTH(REPLACE(team, 'g', ''))) 
       / CHAR_LENGTH('g') AS g_count
FROM athletes;

  team   | g_count 
---------+---------
 Mavs    |       0
 Hawks   |       0
 Kings   |       1
 Kings   |       1
 Celtics |       0
 Nuggets |       2
 Nuggets |       2
 Hawks   |       0

Notice that the resulting table contains a new column named g_count that contains the count of occurrences of the character ‘g’ in the corresponding string in the team column.

For example, we can see:

• The character ‘g’ occurs 0 times in the string Mavs.
• The character ‘g’ occurs 0 times in the string Hawks.
• The character ‘g’ occurs 1 time in the string Kings.

And so on.

Feel free to change the ‘g’ to any specific character that you would like to count.

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