By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
mycolumn VARCHAR(100)
);
INSERT INTO mytable (mycolumn) VALUES
('Brisbane.Wastewater.Fortitude.Valley.Pump.Station.ABC123'),
('Townsville.Water.Northern.Paluma.Reservoir.DEF456'),
('Cairns.Wastewater.Suburb.Asset.XYZ789');
Records: 3 Duplicates: 0 Warnings: 0
SELECT
SUBSTRING_INDEX(mycolumn, '.', 1) AS City,
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, '.', 2), '.', -1) AS Department,
CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, '.', 3), '.', -1), ', ', SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, '.', 4), '.', -1)) AS Site,
SUBSTRING_INDEX(mycolumn, '.', -1) AS Outstation
FROM mytable;
| City | Department | Site | Outstation |
|---|---|---|---|
| Brisbane | Wastewater | Fortitude, Valley | ABC123 |
| Townsville | Water | Northern, Paluma | DEF456 |
| Cairns | Wastewater | Suburb, Asset | XYZ789 |
SELECT
SUBSTRING_INDEX(mycolumn, '.', 1) as City,
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, '.', -4), '.', 1) as Department,
REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, '.', -3), '.', 1), ',', ', ') as Site,
SUBSTRING_INDEX(mycolumn, '.', -1) as Outstation
FROM mytable;
| City | Department | Site | Outstation |
|---|---|---|---|
| Brisbane | Valley | Pump | ABC123 |
| Townsville | Northern | Paluma | DEF456 |
| Cairns | Wastewater | Suburb | XYZ789 |
SELECT
SUBSTRING_INDEX(mycolumn, '.', 1) AS City,
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, '.', 2), '.', -1) AS Department,
TRIM(BOTH '.' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, '.', 4), '.', -1)) AS Site,
SUBSTRING_INDEX(mycolumn, '.', -1) AS Outstation
FROM mytable;
| City | Department | Site | Outstation |
|---|---|---|---|
| Brisbane | Wastewater | Valley | ABC123 |
| Townsville | Water | Paluma | DEF456 |
| Cairns | Wastewater | Asset | XYZ789 |
SELECT
SUBSTRING_INDEX(mycolumn, '.', 1) AS City,
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, '.', 2), '.', -1) AS Department,
REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, '.', -3), '.', 1), CONCAT('.', SUBSTRING_INDEX(mycolumn, '.', -1)), '') AS Site,
SUBSTRING_INDEX(mycolumn, '.', -1) AS Outstation
FROM mytable;
| City | Department | Site | Outstation |
|---|---|---|---|
| Brisbane | Wastewater | Pump | ABC123 |
| Townsville | Water | Paluma | DEF456 |
| Cairns | Wastewater | Suburb | XYZ789 |
SELECT
SUBSTRING_INDEX(mycolumn, '.', 1) AS City,
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, '.', 2), '.', -1) AS Department,
REPLACE(
SUBSTRING_INDEX(
SUBSTRING_INDEX(mycolumn, '.', LENGTH(mycolumn) - LENGTH(REPLACE(mycolumn, '.', '')) + 1),
'.',
LENGTH(SUBSTRING_INDEX(mycolumn, '.', LENGTH(mycolumn) - LENGTH(REPLACE(mycolumn, '.', '')) + 1)) - LENGTH(REPLACE(SUBSTRING_INDEX(mycolumn, '.', LENGTH(mycolumn) - LENGTH(REPLACE(mycolumn, '.', '')) + 1), '.', ''))
),
'.',
', '
) AS Site,
SUBSTRING_INDEX(mycolumn, '.', -1) AS Outstation
FROM mytable;
| City | Department | Site | Outstation |
|---|---|---|---|
| Brisbane | Wastewater | Brisbane, Wastewater, Fortitude, Valley, Pump, Station | ABC123 |
| Townsville | Water | Townsville, Water, Northern, Paluma, Reservoir | DEF456 |
| Cairns | Wastewater | Cairns, Wastewater, Suburb, Asset | XYZ789 |