Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Splitting a String into Rows

This tip 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.


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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.