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 (notice 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
First of all, Welcome emmah to DP... I suggest to you to write small PHP program that read in and update simulteniously the database, with the power of PHP it would be very easy to solve your problem. Another thing I suggest is just read from backward that try extracing last 3 digit and put space there... just a suggestion
As above, also to be more specific, may be you could use ereg to match whether it's AA9 or AA99, then process accordingly.
I'm not familiar with MySQL but sure it has something like length() and substring() functions, which should be enough.