SQL MDN Docs How to emulate split_part() function in MySQL

How to emulate split_part() function in MySQL

In PostgreSQL, the split_part() function is a handy way to split a string on a delimiter and retrieve a specific part. Here’s a simple example:

SELECT
  split_part(email, '@', 1) AS username,
  split_part(email, '@', 2) AS email_domain
FROM users

However, MySQL does not have a direct equivalent to split_part(). Instead, we can emulate its behavior using a combination of other string functions available in MySQL like SUBSTRING_INDEX().

Syntax

Here’s the basic idea:

use SUBSTRING_INDEX() once to shorten the string up to the nth delimiter
then use SUBSTRING_INDEX() again to retrieve the last part from the shortened string

Let’s start by understanding the SUBSTRING_INDEX() function.

What is SUBSTRING_INDEX()

Here’s the basic syntax:

SELECT SUBSTRING_INDEX(string, delimiter, count)

The SUBSTRING_INDEX() function returns the substring from the string str before the count occurrence of the delimiter delim.

If count is positive, it returns everything to the left of the final delimiter (counting from the left).

If count is negative, it returns everything to the right of the final delimiter (counting from the right).

Here’s a simple example:

SELECT SUBSTRING_INDEX('red,blue,green,yellow', ',', 2)

This returns 'red,blue' — the first two parts before the second comma.

Another example:

SELECT SUBSTRING_INDEX('red,blue,green,yellow', ',', -1)

This returns 'yellow' — the last part after the last comma.

Emulating split_part()

Now, to emulate split_part(string, delimiter, part_number), we can combine two calls to SUBSTRING_INDEX().

General approach:

SELECT
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(string_column, delimiter, part_number),
    delimiter,
    -1
  )
FROM table_name

Let’s continue with our color example and extract the 2nd item from the list using the split_part() function:

SELECT split_part('red,blue,green,yellow', ',', 2)
--- blue

In MySQL, we emulate this:

SELECT
  SUBSTRING_INDEX(
    SUBSTRING_INDEX('red,blue,green,yellow', ',', 2),
    ',',
    -1
  )

The first call to SUBSTRING_INDEX() selects the first two parts red,blue and the second call with -1 count argument selects the last part blue.