The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Getting a Word Count
[Deleted User] Administrator
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)