📟
Charl•se

Count words and characters in a string with PostgreSQL

Count words

To count the number of words in a sentence, you can use the REGEXP_SPLIT_TO_ARRAY function to split the sentence on spaces (with regex character \s), and then use ARRAY_LENGTH to count the number of items in the resulting array. For example:

/* Count words separated by whitespaces */
SELECT ARRAY_LENGTH(REGEXP_SPLIT_TO_ARRAY(string_field, '\s'), 1)
  FROM your_table

Count characters

Counting all characters in a field with PostgreSQL is even easier. Just use the LENGTH function, like this:

/* Count characters */
SELECT LENGTH(string_field)
  FROM your_table