469,964 Members | 1,666 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,964 developers. It's quick & easy.

MYSQL Query Help Please

2
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
Sep 1 '06 #1
1 1346
Banfa
9,065 Expert Mod 8TB
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.
Sep 2 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Lenz Grimmer | last post: by
reply views Thread by John R | last post: by
reply views Thread by Philip Stoev | last post: by
51 posts views Thread by w_curtis | last post: by
2 posts views Thread by pratchaya | last post: by
39 posts views Thread by Mairhtin O'Feannag | last post: by
1 post views Thread by Ike | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.