How to Split a Delimited String to Access Individual Items in MySQL?

In MySQL, it’s common to encounter scenarios where we need to split a delimited string into individual items to process or manipulate them separately. This can be achieved using the various techniques and functions provided by MySQL. This article will explore splitting a delimited string and accessing individual items in MySQL.

Splitting Delimited Strings in MySQL

We can use the SUBSTRING_INDEX() function along with the other string manipulation functions like SUBSTRING() and LENGTH(). The basic syntax for splitting a delimited string is as follows:

Syntax:

SELECT SUBSTRING_INDEX(string, delimiter, occurrence) AS individual_item

FROM table_name;

• string: The original string that needs to be split.
• delimiter: The character or substring used to delimit the individual items in the string.
• occurrence: The occurrence of the delimiter to split the string. Use a positive integer to get items from the beginning of the string and a negative integer to get items from the end.

Examples of Splitting Delimited Strings in MySQL

We can create a sample database with a table similar to the employee table mentioned in the examples. Here’s an example of how you can create a sample database and table:

• Create a new database

CREATE DATABASE sample_database;

• Use the newly created database

USE sample_database;

• Create a table named ’employee’ with a column ‘skills’

CREATE TABLE employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    skills VARCHAR(255)
);

• Insert some sample data into the ’employee’ table

INSERT INTO employee (name, skills) VALUES
      ('John', 'Java,C++,Python'),
      ('Alice', 'HTML,CSS,JavaScript'),
      ('Bob', 'MySQL,PostgreSQL,SQLite');

Example 1: Splitting a Comma-Separated String

This query splits the skills string into the individual skills using a comma as the delimiter. It extracts the first three skills from the skills column and assigns them to skill_1, skill_2, and skill_3 respectively.

SELECT 
    SUBSTRING_INDEX(skills, ',', 1) AS skill_1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(skills, ',', 2), ',', -1) AS skill_2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(skills, ',', 3), ',', -1) AS skill_3
FROM 
    employee;

Output:

Splitting a Comma-Separated String

Explanation: The provided SQL query extracts individual skills from the ‘skills‘ column in the ’employee’ table. The output includes three columns: ‘skill_1‘, ‘skill_2′, and ‘skill_3‘, representing the first, second, and third skills respectively, separated by commas in each employee’s skill set.

Example 2: Splitting a Pipe-Separated String

This query splits the skills string into the individual skills using pipe | as the delimiter. It extracts the first three skills from the skills column and assigns them to skill_1, skill_2, and skill_3 respectively.

SELECT 
    SUBSTRING_INDEX(skills, '|', 1) AS skill_1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(skills, '|', 2), '|', -1) AS skill_2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(skills, '|', 3), '|', -1) AS skill_3
FROM 
    employee;

Output:

Splitting a Pipe-Separated String

Explanation: The given SQL query extracts individual skills from the ‘skills’ column in the ’employee’ table, using the pipe ‘|’ character as the delimiter. The output includes three columns: ‘skill_1’, ‘skill_2’, and ‘skill_3‘, representing the first, second, and third skills, respectively, for each employee.

Conclusion

In MySQL, splitting a delimited string into individual items is a common requirement and it can be achieved efficiently using SUBSTRING_INDEX() function along with the other string manipulation functions. By understanding and utilizing these techniques we can efficiently process and manipulate delimited strings in MySQL queries.