Convert Delimited Data Into Columns In MySQL

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:

Similar implementation in other RDBMS platforms –

3 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s