Blog

When working with strings in MySQL, there may be times when you need to split a string into individual components based on a delimiter. For example, you might have a comma-separated list of values and need to extract a specific value from the list. MySQL does not have a built-in function for splitting strings, but you can create a custom function to achieve this. In this blog post, we’ll explore how to create and use a SPLIT_STR function in MySQL.

Creating the SPLIT_STR Function

The SPLIT_STR function takes three parameters:

1. x: The string to be split.
2. delim: The delimiter used to split the string.
3. pos: The position of the part to be extracted (1-based index).

Here is the SQL code to create the SPLIT_STR function:

CREATE FUNCTION `SPLIT_STR`( 
  x text,
  delim VARCHAR(12),
  pos INT) RETURNS text CHARSET utf8mb4
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '')

Let’s break down what this function does:

• SUBSTRING_INDEX(x, delim, pos): This function returns the substring from the beginning of the string x to the pos occurrence of the delimiter delim.
• CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1: This calculates the starting position of the desired substring.
• SUBSTRING(SUBSTRING_INDEX(x, delim, pos), CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1): This extracts the desired substring.
• REPLACE(..., delim, ''): This removes any remaining delimiters from the extracted substring.

Using the SPLIT_STR Function

Once you’ve created the SPLIT_STR function, you can use it to split strings in your queries. Here are a few examples:

Extracting the first part of a comma-separated string:

SELECT SPLIT_STR('apple,banana,orange', ',', 1) AS result;

Output:

result
------
apple

Extracting the second part of a comma-separated string:

SELECT SPLIT_STR('apple,banana,orange', ',', 2) AS result;

Output:

result
------
banana

Extracting the third part of a comma-separated string:

SELECT SPLIT_STR('apple,banana,orange', ',', 3) AS result;

Output:

result
------
orange

You can also test the function with other delimiters and strings:

Using a different delimiter:

SELECT SPLIT_STR('one|two|three|four', '|', 4) AS result;

Output:

result
------
four

Handling more complex strings:

SELECT SPLIT_STR('name=John;age=30;city=New York', ';', 2) AS result;

Output:

result
------
age=30

By using the SPLIT_STR function, you can easily extract specific parts of a string based on a delimiter, making it a powerful tool for string manipulation in MySQL.

Summing Everything Up

The custom SPLIT_STR function in MySQL is a handy solution for splitting strings based on a delimiter. By understanding how to create and use this function, you can simplify many string manipulation tasks in your database queries. Whether you’re dealing with comma-separated values or any other delimiter, the SPLIT_STR function can help you efficiently extract the information you need.

---

---

---

---