Vertica Quick Tip: Splitting a String into Rows

Posted March 12, 2018 by Soniya Shah, Information Developer

Shot of two businesswomen using a digital tablet together during a collaboration at work
This blog post was authored by Jim Knicely. The Vertica Text Search feature includes a handy function named StringTokenizerDelim that you can use to split a string into rows by a given delimiter. Example: dbadmin=> SELECT * FROM test; c1 | c2 ----+------- 1 | A|B|C 2 | D|E|F (2 rows) dbadmin=> SELECT c1, words FROM (SELECT c1, StringTokenizerDelim(c2, ‘|’) OVER (PARTITION BY c1 ORDER BY c1) dbadmin(> FROM test) foo dbadmin-> ORDER BY 1, 2; c1 | words —-+——- 1 | A 1 | B 1 | C 2 | D 2 | E 2 | F (6 rows) Have Fun!