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 |