Using the SPLIT_PART Function

[JimKnicely]

The SPLIT_PART function in Vertica can be used to split a string on a delimiter into multiple parts and return which ever part you want.

It really comes in handy if you need to break out the first, last and middle names from a text field containing a full name.

Example:

Code: Select all

dbadmin=> SELECT full_name,
dbadmin->        split_part(full_name, ',', 1) last_name,
dbadmin->        split_part(trim(split_part(full_name, ',', 2)), ' ', 1) first_name,
dbadmin->        split_part(trim(split_part(full_name, ',', 2)), ' ', 2) middle_name
dbadmin->    FROM names
dbadmin->   ORDER
dbadmin->      BY full_name;
     full_name      |  last_name  | first_name | middle_name
--------------------+-------------+------------+-------------
Dzurek, Jessica M  | Dzurek      | Jessica    | M
Kierzkowski, Tom J | Kierzkowski | Tom        | J
Knicely, Jim O     | Knicely     | Jim        | O
Knicely, Joshua J  | Knicely     | Joshua     | J
Light, Tom L       | Light       | Tom        | L
Watts, Jessica L   | Watts       | Jessica    | L
(6 rows)

Have fun!

[scutter]

Be careful about using SPLIT_PART in a predicate - where it executes on many many rows. Having recently rescued a cluster from such a query's strangle-hold… If you find you need SPLIT_PART in a predicate, better to make a data model change.