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:

The last 3 digits before space? Is this what u asking? – Punitha Subramani Sep 03 '14 at 11:49 — Punitha Subramani, Sep 03 '14 at 11:49

score 2 · Accepted Answer · answered Sep 03 '14 at 12:09

2

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)

answered Sep 03 '14 at 12:09

asontu

Swap LEN for CHAR_LENGTH in PostgreSQL? – Theo F Jun 04 '20 at 16:51
1

@TheoF I'm unfamiliar with PostgreSQL but Googling a bit yes it sounds like that would be the way to go. – asontu Jun 05 '20 at 13:59

score 0 · Answer 2 · answered May 05 '22 at 13:56

0

For MySQL, this worked for me (Canadian Postal Code):

...
UPPER(CONCAT(SUBSTRING(replace(postal_code, ' ', ''), 1, 3), ' ', SUBSTRING(replace(postal_code, ' ', ''), 4, 3)))

answered May 05 '22 at 13:56

Stephane

2 Answers2