mysql split string using 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)

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;

So our main query is now long as well as difficult to read, but this is works for me. Source : MySQL SUBSTRING_INDEX() function.

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.