Splitting a String into Rows

[Deleted User]

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!