Split a string into parts

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser, VerticaPartners

This blog post was authored by Jim Knicely.

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)
Sign In or Register to comment.