PostgreSQL: How to Count Number of Words in String

Often in PostgreSQL you may want to count the number of words in a string.

You can use the following syntax to do so:

SELECT *,
        array_length(regexp_split_to_array(team, E'\\s+'), 1) AS word_count
FROM athletes;

This particular example will create a new column named word_count that counts the number of words in the string of the team column in the athletes table.

Here is how this syntax works:

First, we use the regexp_split_to_array() function to split the string in the team column based on where one or more spaces ( E’\\s+’ ) occur.

Then, we use the array_length() function to count the number of elements in the resulting array.

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

Example: How to Count Number of Words 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 West 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 West Rockets are from Houston
  4 | The Kings from Sacramento
  5 | The Celtics  are from Boston

Suppose that we would like to count the number of words in each string in the team column.

We can use the following syntax to do so:

SELECT *,
        array_length(regexp_split_to_array(team, E'\\s+'), 1) AS word_count
FROM athletes;

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

Notice that the resulting table contains a new column named word_count that contains the count of words in the corresponding string in the team column.

You can use similar syntax to count the number of words in a string column of your own table in PostgreSQL.

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