This article addresses the conversion of the delimited data into columns in MySQL. There are is a function called “SUBSTRING_INDEX” that helps us convert the delimited data into multiple columns.
Consider the below data set for practice.
Data
1456, ConvertedData1, SomeOtherData1
1466, ConvertedData2, SomeOtherData2
1476, ConvertedData3, SomeOtherData4
SUBSTRING_INDEX – Description:
MySQL SUBSTRING INDEX() returns the substring before the specified number of occurrences of a delimiter from the provided string. If the specified number is a positive number, the substring returns from the left of the final delimiter and from the right of the final delimiter when the specified number is a negative number. If the specified number reaches the delimiter’s occurrence number, the total string will be returned. If the number defined is 0, nothing from the given string will be retrieved.
Syntax:
SUBSTRING_INDEX(string, delimiter, count)
Let’s begin the exercise:
— Creating a table
CREATE TABLE splitStringData(dataCol1 VARCHAR(1000))
— Inserting values into the table
INSERT INTO splitStringData VALUES
(‘1456, ConvertedData1, SomeOtherData1’),
(‘1466, ConvertedData2, SomeOtherData2’),
(‘1476, ConvertedData3, SomeOtherData4’);
— Querying
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(dataCol1, ‘,’, 1), ‘,’, -1) AS col1 ,
SUBSTRING_INDEX(SUBSTRING_INDEX(dataCol1, ‘,’, 2), ‘,’, -1) AS col2,
SUBSTRING_INDEX(SUBSTRING_INDEX(dataCol1, ‘,’, 3), ‘,’, -1) AS col3
FROM splitStringData;
Results:
col1 col2 col3
1456 ConvertedData1 SomeOtherData1
1466 ConvertedData2 SomeOtherData2
1476 ConvertedData3 SomeOtherData4
Similar implementation in other RDBMS platforms –
Big Data and SQL 
3 comments