Paterns in MySQL

[RicardoE]

Hello dear community,

I have a problem which I have no idea how to walk around, see I have a really large database, with france zip codes, I imported this data from excel. the thing is that when it imported all the zip codes starting by 0 are not starting with 0 any more.

so for example:

zip code 01000 is now 1000, zip codes must have allways 5 digits,
how can I do yo insert a 0 at the begining to all the zip codes with 4 digits?

your help is must appreciated.

I've already looked at the MySQL documentation, and it only shows examples for pattern matching in SELECT querys, I want something with UPDATE querys.

thanks.

[smantscheff]

It depends on your data definition.
If you defined the zip codes as integers you cannot have leading zeros. You will have to add them in processing the data.
If you defined the zip codes as character fields you can use the implicit conversion or the convert function:

update mytable set zipcode = concat("0",zipcode) where zipcode < 10000;