472,126 Members | 1,642 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,126 software developers and data experts.

MYSQL Query Help Please

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.

Sep 1 '06 #1
1 1397
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
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.