List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 12 2000 7:53pm
Subject:Re: newbie question add zero before certain numbers
View as plain text  
>I've created a database that holds zip codes. For reasons unclear at this
>time I changed my zip code column from a mediumint to a varchar which
>screwed up the zips proceeded by a zero, ie. 04412 became 4412. Is there a
>way I can find all zips less then 10000 and add a zero to them?

UPDATE tbl_name SET zip = LPAD(zip, 5, "0")

That will add a sufficient number of "0" characters on the left to
make all your strings 5 characters long.  It won't affect values
that already have 5 characters.

>
>On a similar note. What's the best way to deal with zip+4 numbers, ie.
>44012-1203. Should there be two columns and keep them both integers?

That's up to you.  You could just as well use VARCHAR(10) or CHAR(10).
If you use two integers, you'll probably have more complex application
logic.  Depends on what kinds of operations you intend to perform on
the values.

-- 
Paul DuBois, paul@stripped
Thread
newbie question add zero before certain numbersTom Beidler12 Dec
  • Re: newbie question add zero before certain numbersPaul DuBois12 Dec