Getting a Word Count
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.
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)