alter table `servicejob`
  add column `street` varchar(200) not null default '' after `vanId`;
CREATE INDEX `street` ON `servicejob`(`street`) USING BTREE ;

alter table `servicejob`
  add column `city` varchar(65) not null default '' after `street`;
CREATE INDEX `city` ON `servicejob`(`city`) USING BTREE ;

alter table `servicejob`
  add column `region` varchar(30) not null default '' after `city`;
CREATE INDEX `region` ON `servicejob`(`region`) USING BTREE ;

alter table `servicejob`
  add column `postCode` varchar(10) not null default '' after `region`;
CREATE INDEX `postCode` ON `servicejob`(`postCode`) USING BTREE ;

UPDATE servicejob
SET postCode = SUBSTRING_INDEX(address, ', ', -1), updated = updated
WHERE SUBSTRING_INDEX(address, ', ', -1) REGEXP '^[0-9]+$';

UPDATE servicejob
SET region = SUBSTRING_INDEX(SUBSTRING_INDEX(address, ', ', -3), ', ', 1), updated = updated
WHERE LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(address, ', ', -3), ', ', 1)) = 3;

UPDATE servicejob
SET region = SUBSTRING_INDEX(SUBSTRING_INDEX(address, ', ', -2), ', ', 1), updated = updated
WHERE LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(address, ', ', -2), ', ', 1)) = 3
      AND region = '';
