We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Getting a Word Count — Vertica Forum

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.

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!

Sign In or Register to comment.