Dear Everyone,
I have a small but hopefully simple problem. Please forgive my misuse of technical terminology but I am hoping someone can provide some advise.
We have a database of nearly 1.6 million UK business address (MYSQL) all with post codes (zip codes). Post codes can have either 6 or 7 digits, for example: RG28PL or TR129BM (noptice no spaces).
I need them to be in the following format:
RG2(space)8PL or TR12(space)9BM - or looking like this: RG2 8PL and TR12 9BM
The only consistent that I can see is that it is always the last 3 digits after the space. So at the begining is could have 3 or 4 digits followed always by the last 3 digits.
I could do this manualy but that would take me until I am 30 (I am 22 now) so I was hoping that I could use a query to amend all the post codes to read exactly as above?
Just to further complicate this there are 'some' (an unknown quantity) that are in the correct format so I dont wish for these to change otherwise there would be a double space and not the one.
The table coloumn is post_codes in this particular table.
Is this possible?
Thank you in advance for any help and advice.
EmmaH
Comments
[code]
UPDATA table_name
SET post_codes = INSERT(TRIM(post_codes), 3, 0, ' ')
WHERE CHARACTER_LENGTH(post_codes) = 6;
[/code]
I am not sure what you wana do with 7chars-length-string, but it s basically the same.
Yeap, that s it, i guess.