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!

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.

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!

Sign In or Register to comment.