MYSQL Query Help Please

[emmah]

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 diguts 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

[Banfa]

I am thinking something like

UPDATE table SET PostCode=CONCAT( LEFT(PostCode,LENGTH(PostCode)-3), ' ', RIGHT(PostCode,3)) WHERE LOCATE(' ',PostCode)=0

May work but back up your database before trying it.