MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE 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');

Records: 2  Duplicates: 0  Warnings: 0


CREATE TABLE numbers (
  n INT PRIMARY KEY);
INSERT INTO numbers VALUES (1),(2), (3), (4), (5), (6);

Records: 6  Duplicates: 0  Warnings: 0


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


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

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