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

minhnguyena7

your query is simply is: insert a space to the zipcode which you can use function: INSERT(string, position, length, new_string) to UPDATE statment, in your case is
[code]
UPDATA table_name
SET post_codes = INSERT(TRIM(post_codes), 3, 0, ' ')
WHERE CHARACTER_LENGTH(post_codes) = 6;
[/code]

I am not sure what you wana do with 7chars-length-string, but it s basically the same.
Yeap, that s it, i guess.