Summarize the Values in a String

Jim_Knicely

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;
 add_these
-----------
 10,20,30
 5,4,100
 1,-1,1
(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)