Split a String in MySQL with the SUBSTRING_INDEX() Function

mysql split string : Let’s step by step with the best “Splitting Strings with MySQL” example of a multi-line location.

Syntax : mysql split string

SUBSTRING_INDEX(str, delim, count)

Example : mysql split string

SELECT SUBSTRING_INDEX('www.pakainfo.com','.',2);

mysql split string (Splitting Strings)

Contents

Here is our sample data location mysql table in my database:

CREATE TABLE `location` (
`id` INTEGER AUTO_INCREMENT PRIMARY KEY,
`profile_name` VARCHAR(255),
`company` VARCHAR(255),
`venue` VARCHAR(255),
`city` VARCHAR(255),
`state` VARCHAR(255),
`zip` VARCHAR(20),
`country` VARCHAR(255)
);

And here is the inserted data:

INSERT INTO `location` VALUES (
NULL,
'Love Zakhi',
'zakh, Inc',
'985 zakh mag\nPakai 786',
'San digolala',
'California',
'98256',
'United States'
);

Let’s say i want to query this main mysql table and mysql split string the venue details into multiple columns, therefor that each line is its single column. I can do this using the mysql main methods of the SUBSTRING_INDEX function in MySQL.

SELECT SUBSTRING_INDEX(venue, '\n', 1) AS venue1, SUBSTRING_INDEX(venue, '\n', 2) AS venue2, SUBSTRING_INDEX(venue, '\n', 3) AS venue3 FROM location;

But, It is not great or quite work yet.

simple run this example Here is what is returned:

venue1 venue2 venue3
985 zakh mag 985 zakh mag↵Pakai 786 985 zakh mag↵Pakai 786

Our final latest query now looks like this example bellow:

SELECT
SUBSTRING_INDEX(venue, '\n', 1) AS venue1,
SUBSTRING_INDEX(SUBSTRING_INDEX(venue, '\n', 2), '\n', -1) AS venue2,
SUBSTRING_INDEX(SUBSTRING_INDEX(venue, '\n', 3), '\n', -1) AS venue3
FROM location;

I am almost there, but not quite. You can display that venue3 is returning the same value as venue2:

venue1 venue2 venue3
985 zakh mag Pakai 786 Pakai 786

It is doing this because there are only two lines in our venue. In order to account for this, we need to count the number of lines, then use IF statements to control our SUBSTRING_INDEX:

mysql split string Example

SELECT
@num_venue_lines := 1 + LENGTH(venue) - LENGTH(REPLACE(venue, '\n', '')) AS num_venue_lines,
SUBSTRING_INDEX(venue, '\n', 1) AS venue1,
IF(@num_venue_lines > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(venue, '\n', 2), '\n', -1), '') AS venue2,
IF(@num_venue_lines > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(venue, '\n', 3), '\n', -1), '') AS venue3
FROM location;

And last step for those conditions where you must do all the issue fixed in SQL, as well as cannot use a language like server side PHP to process your mysql query, Good Luck it is very useful for you.

• This blog post was originally published at: https://www.pakainfo.com

#mysql  #sql  #database