
One of my favorite functions in Vertica is named SPLIT_PART. It splits up a string into parts by a given delimiter.
Example:
dbadmin=> SELECT split_part(my_text, ',', 1) the_first_part,
dbadmin-> split_part(my_text, ',', 2) the_second_part,
dbadmin-> split_part(my_text, ',', 3) the_third_part,
dbadmin-> split_part(my_text, ',', 4) the_fourth_part
dbadmin-> FROM (SELECT 'ONE,TWO,THREE,FOUR' my_text) foo;
the_first_part | the_second_part | the_third_part | the_fourth_part
----------------+-----------------+----------------+-----------------
ONE | TWO | THREE | FOUR
(1 row)
But what if I don’t know how many parts there are in my text? As the following example shows, I can use the ROW NUMBER analytic function to dynamically split my text up!
dbadmin=> SELECT * FROM split_this_up ORDER BY 1;
id | some_text
----+----------------------------------------
1 | Please split this sentence up by word
2 | And do the same with this sentence too
(2 rows)
dbadmin=> SELECT id, word
dbadmin-> FROM (SELECT id, split_part(some_text, ' ', row_number() over (PARTITION BY id)) word
dbadmin(> FROM split_this_up
dbadmin(> CROSS JOIN columns) foo
dbadmin-> WHERE word <> '';
id | word
----+----------
1 | Please
1 | split
1 | this
1 | sentence
1 | up
1 | by
1 | word
2 | And
2 | do
2 | the
2 | same
2 | with
2 | this
2 | sentence
2 | too
(15 rows)
Have fun!