Mysql query help

  emmah 14:58 01 Sep 2006

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.


  xania 16:22 01 Sep 2006

I'm sure there's an easier way, but let me analyse the problem first. You can have 6 chars (incl. no spaces), 7 chars (incl. no spaces) 7 chars (incl. 1 space) 0r 8 chars (incl. 1 space). OK now here's the multi-stage approach.

You can use the function 'LEN' to determine the length of each, then you can sort of this result to isolate all the 6s and 8s into separate lists. Leave the 8's alone but split the 6's into 2*3 char fields and insert a blank field then merge the three into one. Now for the 7's.

Split into 7 individual columns and sort on column 4. All the top ones will have a space in column 7 - these are already ok - remerge the fields. All the rest need you to insert a blank single char field and remerge.

NB to create a single char blank, use " ".

Haven't tried this but it should work. Good luck.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment regarder des séries et talk-shows américains en France ?