Count Number of words in PostgreSQL

To count the number of words in a column in PostgreSQL, you can use a combination of string functions and regular expressions. Here’s an example using the regexp_split_to_table() function to split the string into words and then count them. Alternate method is by using string_to_array() with array_length

• Count the number of words in the string in PostgreSQL
• Count the number of words in the string column in PostgreSQL

Method 1: Using regexp_split_to_table():

To count the number of words in the string using regexp_split_to_table() function you can use the following query:

 

SELECT count(*) FROM regexp_split_to_table('Hello world, welcome to world of PostgreSQL.', '\s+');

Output:

Count words in PostgreSQL Column using  regexp_split_to_table() :

We will be using table named “texts”

To count the number of words in each content field, you can use the following query:

 

SELECT *, (Select count(*) FROM regexp_split_to_table(content, '\s+') ) AS word_count
FROM texts;

Explanation

• regexp_split_to_table(content, ‘\s+’) splits the content string into words based on whitespace (\s+ matches one or more whitespace characters).
• The subquery counts the number of rows returned by regexp_split_to_table, effectively giving you the number of words.

Which will create the column with count of words

Output:

Method 2: Using string_to_array() along with array_length

To count the number of words in the string using string_to_array() function you can use the following query, which will convert the string to array and we compute array length using array_length() function as shown below

 

select array_length(string_to_array('Hello world, welcome to world of PostgreSQL.', ' '), 1);

Output:

Count number of words in PostgreSQL Column using  string_to_array() :

We will be using table named “texts”

To count the number of words in each content field, you can use the alternate method with string_to_array combined with array_length:

 

SELECT *, array_length(string_to_array(content, ' '), 1) AS word_count
FROM texts;

This method splits the content into an array of words based on spaces and then uses array_length to count the number of elements in the array. This method assumes words are separated by single spaces and does not handle multiple spaces or punctuations.

Output: