Use of Split_Part in Postgres

Introduction

In this tutorial, we will learn how to use Split_Part in Postgres SQL. We’ll also explore the array data type, the array_to_string function, and the string_to_array function. In this lesson, we’ll use the following methodology:

  • What? What is the description of and syntax for using the Split_Part function and Array data type?
  • Why? When and how do we use Split_Part and Arrays in our PostgreSQL SQL?
  • Additional As Split_Part is a function, there are many ways to use it. We’ll learn a few of those ways and even practice using a real world example. Before we learn about Split_Part, we’ll explore a closely related Postgres type called “Array”.

Prerequisite: Understanding Arrays

An array is a list or group of items. In the database programming world, arrays often increase efficiency and save us work when dealing with list of numbers or text. You can call “1, 4, 5, 7, 8” an array of integers or numeric items (and they can be in any order you want; not needing to be consecutive, like you see in that list). You can call “Donald Duck, Descending, Ascending, Roadrunner, Chrome, Database, Function” an array of strings or array of text items. “Items” is often used to describe each individual in the group that is an array or other kind of list.

In Postgres’ array is data type. So in one cell (cross-section of a row and column), you can have – instead of an integer or text data type – an array data type. Here’s how you would create a column to be an array:

CREATE TABLE tbl_users (
    t_name_user text -- not this column
    , a_i_grade INTEGER[] -- this column is the array
)

In that SQL above, the column “ai_grade” may be new for you. The brackets (“[]”) are how we tell Postgres “type this column as an array.” We named the column with a prefix of “a” to designate “array” as the primary type, then “i_” to designate the secondary type of “integer”.

Looking at that table filled with some test data, we get a visual perspective on the concept of how array[] works in Postgres databases.

SELECT
    t_name_user text
    , a_i_grade INTEGER[]
FROM
    tbl_users

Gives us…

t_name_usera_i_grade
Steve40, 65
Bill88, 80, 85
Sharon82, 85, 89
Todd90, 89, 95, 94
George60, 75
Sal77
Fred74, 72, 79, 78

Note: The actual data returned from PostgreSQL included “{}” brackets that we removed so you see a less cluttered and easier to understand view of the returned data.

Now to retrieve data from our table and acquiring only part of the array:

SELECT
    t_name_user text
    , a_i_grade INTEGER[1]
FROM
    tbl_users

Returns the following data:

t_name_usera_i_grade
Steve40
Bill88
Sharon82
Todd90
George60
Sal77
Fred74

Explanation

The [1] in a_i_grade integer[1] tells the PostgreSQL SQL engine to return the first item in the “a_i_grade” column and that column is an array.

Now that you understand the basics of arrays, it’s time to learn how to use Split_Part() to create an array.

Split_Part

Syntax:

SPLIT_PART(t_string_to_split, t_delimiter, i_position_to_get)

  • t_string_to_split: This is a string containing a list of items, such as “1, 3, 5” or “Spreadsheet, Database, Table”.
  • t_delimiter: This is the comma you most often see to “delimit” or separate each item within the string of items. If your list is a phrase and you want to acquire each word, you may wish to use space as a delimiter.
  • i_position_to_get: Which item (start counting at 1) do you want to return?

Example:

SPLIT_PART("cat, dog, mouse", ",", 2)

Returns “dog” because we used “2” as the i_position_to_get parameter and dog is in the 2nd position within the string we supplied to the Split_part() function.

OK that sounded easy. Why then did we spend so much time learning about ARRAYS? Because – instead of supplying t_string_to_split with a literal string, we can supply it with an array, like so:

DECLARE
        a_t_names TEXT[] := ARRAY['Jimmy', 'Timmy', 'Cindy'];
        t_names TEXT := array_to_string(a_t_names, ",");
BEGIN
        RETURN split_part(t_names, ",", 2);
END

Analysis:

  • a_t_names: Here we are declaring our array and filling it with three names.
  • t_names: This is using the Postgres array_to_string function to transform our array into a string that looks like “Jimmy, Timmy, Cindy”.
  • split_part: Here we are asking for the 2nd item in the list to be returned, which happens to be “Timmy”.

Now that we understand how the Split_Part() function and arrays work, let’s take a quick look at a related function, string_to_array().

String_to_array

Syntax:

STRING_TO_ARRAY(t_string_to_split, t_delimiter, [optional null handling])

  • t_string_to_split: This is a list of items, which might look like either “5, 2, 9” or “Variable, Field, Column”.
  • t_delimiter: This is the comma you most often see to separate each item within the string of items. If your list is a phrase and you want to bring each word into your array, you would use space as a delimiter. Note: Keep in mind the phrase supplied might have commas or other special characters, in addition to spaces, separating words.
  • optional null handling: Optional string for null handling.

Example:

a_t_technology = STRING_TO_ARRAY("spreadsheet, database, table", ",")

…fills the a_t_technology text array to look like:

{"spreadsheet", "database", "table"}

Miscellaneous

If you are wondering why we named some columns (fields) and variables with a prefix of “at“, “i“, or “t“: In this lesson, we used “ai” to mean array of integers, “i” to mean integer and “t” to mean text. Here is a tutorial that explains why, including how that practice can increase your skill as a programmer.

Conclusion

In this tutorial, we learned how and why to use the Split_Part function, the Array_to_String function, the String_to_Array function, and the ARRAY data type in Postgres SQL. We also used the DECLARE clause. There is much more we can do with arrays. Look for future articles that show many other ways to use arrays, as well as using all the various array-related functions you can use in PostgreSQL.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.