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:
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:
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.
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.