Split value into multiple rows

The following are two tables I have in the database mavenmovies.

testLocation (TABLE 1)

CREATE TABLE mavenmovies.testLocation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    State varchar(255),
    name varchar(255)
);
INSERT INTO testLocation (State, name)
VALUES 
('Alabama','Birmingham;Huntsville;Mobile;Montgomery'),
('Florida','Tampa;Jacksonville;Destin');

numbers (TABLE 2)

CREATE TABLE mavenmovies.numbers (
  n INT PRIMARY KEY);
INSERT INTO numbers VALUES (1),(2);

This is what I want to query

This is what I currently query (see my current query/attempt below)

My current query/attempt

select
  testLocation.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(testLocation.name, ';', numbers.n), ';', -1) name
from
    numbers, testLocation;

MY QUESTION

How can I expand my query to take care of an unknown amount of delimited values per row? For example, one row may have four, twenty, or zero separated values.

Thank you all for any guidance you can provide.

Answer

You are on the right track with the table of numbers. You should start by adding more rows, so it matches (or exceeds) the maximum possible number of elements in a CSV list.

Then, you can use a join condition to generate the relevant number of rows per name only

select
    t.*,
    substring_index(substring_index(t.name, ';', n.n), ';', -1) name
from numbers n
inner join testLocation t   
    on n <= length(t.name) - length(replace(t.name, ';', '')) + 1

Demo on DB Fiddle (I expanded the numbers to 8):

id | State   | name                                    | name        
-: | :------ | :-------------------------------------- | :-----------
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Birmingham  
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Huntsville  
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Mobile      
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Montgomery  
 2 | Florida | Tampa;Jacksonville;Destin               | Tampa       
 2 | Florida | Tampa;Jacksonville;Destin               | Jacksonville
 2 | Florida | Tampa;Jacksonville;Destin               | Destin      

Note, that, as commented already by others, storing CSV lists in a database is bad practice and should almost always be avoided. Recommended related reading: Is storing a delimited list in a database column really that bad?