CODEWITHSUNDEEP
sql
user922161
user922161

Reputation: 69

Leading Zero for Zipcode

I have a table named address that as a column zipcode (varchar(10)) which consists of data like 722090000.

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?

Upvotes: 2

Views: 12029

Answers (4)

Martin Sansone - MiOEE
Martin Sansone - MiOEE

Reputation: 4399

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 )

Upvotes: 2

reggie
reggie

Reputation: 13731

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

Upvotes: 1

Dave L
Dave L

Reputation: 1634

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.

Upvotes: 3

John Hartsock
John Hartsock

Reputation: 86892

If your using Microsoft SQL Server then use LEN()

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

Upvotes: 1

Related Questions