Getting a Word Count

[Deleted User]

This tip was authored by Jim Knicely.

The Vertica REGEXP_COUNT function returns the number times a regular expression matches a string. You can use it to create your own user defined SQL function that counts the number of words in a string.

Example:

dbadmin=> CREATE OR REPLACE FUNCTION get_word_count(x varchar) RETURN INT
dbadmin-> AS
dbadmin-> BEGIN
dbadmin->   RETURN regexp_count(x, '[\w-]+');
dbadmin-> END;
CREATE FUNCTION

dbadmin=> SELECT get_word_count('The Vertica Analytics Platform is purpose built from the very first line of code for Big Data analytics.');
get_word_count
----------------
             18
(1 row)

dbadmin=> SELECT get_word_count('Vertica allows you to analyze your data not only in place, but in the right place – without data movement – while supporting any major cloud deployment for fast and reliable read and write for multiple data formats!');
get_word_count
----------------
             36
(1 row)

dbadmin=> SELECT get_word_count('Vertica relies on a tested, reliable distributed architecture and columnar compression to deliver blazingly fast speed. A simplified license and the capability to deploy anywhere delivers on the promise of big data analytics like no other solution.');
get_word_count
----------------
             37
(1 row)

Have Fun!