Reformat postcodes to include a space

Question

Is there a reliable process (using MySQL) to reformat UK postcodes which contain no spaces so that they do?

For example:

• CM75GJ -> CM7 5GJ
• L269XR -> L26 9XR
• YO265TD -> YO26 5TD

Answer 1

Looking at UK postal code formatting and assuming there are is no faulty input, this should do the trick:

UPDATE address SET postcode =
    SUBSTRING(postcode, 1, LEN(postcode)-3) + ' ' +
    SUBSTRING(postcode, LEN(postcode)-2, 3)