Splitting a cell in mySQL into multiple rows while keeping the same "ID"

Question

In my table I have two columns "sku" and "fitment". The sku represents a part and the fitment represents all the vehicles this part will fit on. The problem is, in the fitment cells, there could be up to 20 vehicles in there, separated by ^^. For example

**sku   --   fitment**  
part1 --   Vehichle 1 information ^^ vehichle 2 information ^^ vehichle 3 etc

I am looking to split the cells in the fitment column, so it would look like this:

**sku -- fitment** 

part1 -- Vehicle 1 information

part1 -- Vehicle 2 information

part1 -- Vehicle 3 information

Is this possible to do? And if so, would a mySQL db be able to handle hundreds of thousands of items "splitting" like this? I imagine it would turn my db of around 250k lines to about 20million lines. Any help is appreciated!

Also a little more background, this is going to be used for a drill down search function so I would be able to match up parts to vehicles (year, make, model, etc) so if you have a better solution, I am all ears.

Thanks

score 0 · Answer 1 · edited May 23 '17 at 11:59

Possible duplicate of this: Split value from one field to two

Unfortunately, MySQL does not feature a split string function. As in the link above indicates there are User-defined Split function's.

A more verbose version to fetch the data can be the following:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(fitment, '^^', 1), '^^', -1) as fitmentvehicle1,
   SUBSTRING_INDEX(SUBSTRING_INDEX(fitment, '^^', 2), '^^', -1) as fitmentvehicle2
....
   SUBSTRING_INDEX(SUBSTRING_INDEX(fitment, '^^', n), '^^', -1) as fitmentvehiclen
  FROM table_name;

Since your requirement asks for a normalized format (i.e. not separated by ^^) to be retrieved, it is always better to store it in that way in the first place. And w.r.t the DB size bloat up, you might want to look into possibilities of archiving older data and deleting the same from the table.
Also, you should partition your table using an efficient partitioning strategy based on your requirement. It would be more easier to archive and truncate a partition of the table itself, instead of row by row.

Thanks for the reply, Unfortunately, the data is all current and cant be archived. I see how your code would work, but the problem is I have no idea how many vehicles each fitment will contain! Il have a look at the links you posted and see if they will help! — Leathel, Jan 27 '16 at 14:43
In such a case where the number of vehicles is unknown, you can create a Stored Procedure with `for loop` to iterate through the length tokenized by `'^^'`. — Sameer Mirji, Jan 27 '16 at 15:03

score 0 · Answer 2 · answered Jan 27 '16 at 14:47

E.g.

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (user_id INT NOT NULL PRIMARY KEY,stuff VARCHAR(50) NOT NULL);

INSERT INTO my_table VALUES (101,'1,2,3'),(102,'3,4'),(103,'4,5,6');

SELECT * 
  FROM my_table;
+---------+-------+
| user_id | stuff |
+---------+-------+
|     101 | 1,2,3 |
|     102 | 3,4   |
|     103 | 4,5,6 |
+---------+-------+

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT DISTINCT user_id
              , SUBSTRING_INDEX(SUBSTRING_INDEX(stuff,',',i2.i*10+i1.i+1),',',-1) x 
           FROM my_table
              , ints i1
              , ints i2
          ORDER 
             BY user_id,x;
+---------+---+
| user_id | x |
+---------+---+
|     101 | 1 |
|     101 | 2 |
|     101 | 3 |
|     102 | 3 |
|     102 | 4 |
|     103 | 4 |
|     103 | 5 |
|     103 | 6 |
+---------+---+

Splitting a cell in mySQL into multiple rows while keeping the same "ID"

2 Answers2