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!

Summarize the Values in a String

The built-in Text Search feature of Vertica allows you to quickly search the contents of a single CHAR, VARCHAR, LONG VARCHAR, VARBINARY, or LONG VARBINARY field within a table to locate a specific keyword. Included with Text Search are several pre-configured tokenizers. One of the most useful tokenizer splits string data into rows via a specified delimter. This cool feature opens the door to do interesting things like summing a list of integers.

dbadmin=> SELECT * FROM test;
(3 rows)

dbadmin=> SELECT input add_these, SUM(words::INT) FROM (SELECT v_txtindex.StringTokenizerDelim(add_these,',') OVER () FROM test) foo GROUP BY input;
 add_these | SUM
 1,-1,1    |   1
 5,4,100   | 109
 10,20,30  |  60
(3 rows)
Sign In or Register to comment.