tero.co.uk

MySQL String Splitter

Below is a way to split a string by a delimiter in MySQL without using a stored procedure. For example, maybe you have rows like this in a table:

idchoices
1red,green,blue,orange
2blue
3
4red,green

But you need to extract the data like this, maybe so that you can group it and count the occurrences:

choice
red
green
blue
orange
blue
red
green

This page describes a method for doing this. To use the method you will first need to have another table that has numbers from 1 up to however many choices each row can store (in this case 4). This table will be used in a join, so that the first choice will be joined to the row with number 1, the second choice to row 2, etc. So you would need a table like this:

id
1
2
3
4
5
...

Let's say your main table is called maintable with a choices column, and your other table is called othertable with an id column (though you could use any table that had sequential numbers or id numbers). Then you can run an SQL statement like:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(maintable.choices,',',othertable.id),',',-1) AS choice
FROM maintable INNER JOIN othertable ON
    (LENGTH(choices)>0 AND SUBSTRING_INDEX(SUBSTRING_INDEX(choices,',',othertable.id),',',-1)
    <> SUBSTRING_INDEX(SUBSTRING_INDEX(choices,',',othertable.id-1),',', -1));

This will produce results similar to the above. If you order by maintable.id, it will be exactly as above. If you also show the column othertable.id in the results, you'll see that the first choice in each of the sets is joined to the othertable row with id 1. The long part of the ON condition prevents the statement from showing the last choice repeatedly. If your othertable table had 100s or 1000s of rows, this would be very inefficient, and you'd need to add an extra clause like othertable.id<10.

You can set up this example by running:

CREATE TABLE maintable (id INT, choices VARCHAR(255));
INSERT INTO maintable VALUES (1, 'red,green,blue,orange'), (2, 'blue'), (3, ''), (4, 'red,green');
CREATE TABLE othertable (id INT);
INSERT INTO othertable VALUES (1), (2), (3), (4), (5), (6), (7), (8);

You can add a grouping to the above SQL to get a statistical summary:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(maintable.choices,',',othertable.id),',',-1) AS choice,
    COUNT(*) AS numtimes
FROM maintable INNER JOIN othertable ON
    (LENGTH(choices)>0 AND SUBSTRING_INDEX(SUBSTRING_INDEX(choices,',',othertable.id),',',-1)
    <> SUBSTRING_INDEX(SUBSTRING_INDEX(choices,',',othertable.id-1),',', -1))
GROUP BY choice ORDER BY choice;
choicenumtimes
blue2
green2
orange1
red2

This isn't a particularly graceful or efficient method, but it avoids using stored procedures, so I hope you will find it useful.