How to Split a String Using SPLIT_PART() Function in PostgreSQL

PostgreSQL offers a built-in function named SPLIT_PART() that splits the given string based on the specified delimiter. It returns the ‘n’ number of substrings. The SPLIT_PART() function splits the strings from left-to-right.

This post will demonstrate the working of the SPLIT_PART() function in PostgreSQL. Each concept will be explained with practical examples. So, let’s start.

How to Split a String Using SPLIT_PART() Function in PostgreSQL?

The SPLIT_PART() function accepts three parameters: a string, a delimiter, and position. The basic syntax of the SPLIT_PART() will look like this:

SPLIT_PART(str, delimiter, position);

The below-listed points explain the argument’s details:

- str represents a string to be split/broken.

- The delimiter is a string that will be used to break the specified string into substrings.

- While position specifies the parts/substrings to be returned, and it must be a positive number.

Let’s consider some examples to clarify the points mentioned above.

Example #1: How to Use SPLIT_PART() Function in Postgres?

In this example, we will pass three arguments to the SPLIT_PART() function: a string, a comma as a delimiter, and 3 as a position:

SELECT SPLIT_PART('You can find fish, turtles, cats, and dogs in a pet store.', ',', 3);

The SPLIT_PART() function will split the given string into n number of substrings based on the specified delimiter, i.e., “,”. We specified 3 in place of the position parameter, so the SPLIT_PART() function will return the third substring:

The output shows that the SPLIT_PART() function splits the string into parts and returns a substring that is present at the third position.

Example #2: How to Use the SPLIT_PART() Function on Table’s Data?

In this example, we will learn how to use the SPLIT_PART() function on the table's data. Firstly, we will execute the SELECT statement to fetch the table’s data:

SELECT * FROM article_details;

Suppose we have to fetch the day and month from the published_date column. To do so, we will use the SPLIT_PART() function as follows:

SELECT SPLIT_PART(published_date :: TEXT, '-', 2) AS month,
SPLIT_PART(published_date :: TEXT, '-', 3) AS day
FROM article_details;

In the above query we performed the following tasks:

- The SPLIT_PART() function accepts string type data so firstly we convert the date type values into the TEXT data type using the :: operator.

- Next, we specified “-” as a delimiter.

- In the published_date column, the date is specified in YYYY-MM-DD format. So, we specified 2 and 3 in place of the position parameter to get the month and day, respectively.

This way, you can utilize the SPLIT_PART() function on the table’s data.

Conclusion

PostgreSQL offers a built-in function named SPLIT_PART() that splits the given string based on the specified delimiter. It returns the ‘n’ number of substrings. It accepts three parameters: a string to be split/broken, a delimiter based on which the given string will be split, and a position that specifies the substrings to be returned. This post narrated the working of the SPLIT_PART() function using suitable examples.

How to Split a String Using SPLIT_PART() Function in PostgreSQL