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!
About the Author
Soniya Shah
Information Developer
Currently, a first year law student with a background in science and technology. Experienced technical writer, with specializations in software documentation, big data, blog development, and website development. I build user-centered content to communicate complex and technical information more easily.
I used to work for Vertica full time for about 3 years. I still work at Vertica part time while going to law school.
Update: Soniya is now doing her law internship, and no longer working at Vertica. Good luck, Soniya!
