Leading Zero for Zipcode

I have a table named address that as a column zipcode (varchar(10)) which consists of data like 7220900开发者_开发知识库00.

The table has ~10000 records. I want to update the column to add a zero, only for records which look like this - 0722090000 - and their length is greater than 5.

How would I do this?

---

Do you want the entire 10 characters to be filled always? So if the zip code is '123456' it turns into '0000123456'? If that's the case then you can use this if your database supports it:

UPDATE address
  SET zipcode = RIGHT('0000000000' + zipcode, 10)
WHERE LEN(zipcode) > 5

Otherwise the other answers are fine.

---

The following allows you to set the integer number of zeros. I usually use either 5 or 10 and will fill in the spare zeros around the existing numbers.

UPDATE
  [address ]
SET
  [zipcode] = RIGHT( '00000' + LTRIM( RTRIM( [zipcode] ) ), 5 )

---

If your using Microsoft SQL Server then use LEN()

UPDATE address
  SET zipcode = '0' + zipcode
WHERE LEN(zipcode) > 5

---

If you are using mysql, you need the LENGTH() METHOD . It will return the length of the string passed. Example: SELECT LENGTH('text'); will return 4.

You need the following query:

 UPDATE address SET zipcode = '0' + zipcode WHERE length(zipcode) > 5

And as John Hartsock mentioned in his answer, if you are using MS SQL, you can use the LEN() method