Vertica Quick Tip: Getting a Word Count

This blog post 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!