Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-19 : 08:53:52
|
| Patrick writes "Hi, I have a table that has a column defined as numeric. This table resides on a SQL 2000 box (SP2) running 2000 Adv. Server (SP2). When I pass an update:"update my_table set zipcode = 01254"The zero is chopped off and the record contains '1254' in the zipcode column. Why is that? Is SQL saving space by truncating the 0? If so, this is going to sound stupid, but what other datatype should be used for zipcodes or phone numbers (just to name a few)?Best Regards,Patrick" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-19 : 09:03:05
|
| You're confusing display format with storage value. Numbers don't have leading zeros, no one writes them that way. A zip code is a code, not a numeric value (like age, weight, quantity) Social security numbers are also codes, and need to keep the leading zeros.If you have a code that needs to preserve leading zeros, use varchar or char data types.Edited by - robvolk on 04/19/2002 09:03:37 |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-19 : 10:46:40
|
| For zip codes: varchar(9) with a constraint specifying only 0-9 and a length of 5 or 9.For phones: three columns, npa char(3), nxx char(3), station char(4). The npa is what most people call the 'area code', the nxx is what most people call the 'prefix'.setBasedIsTheTruepath<O> |
|
|
|
|
|
|
|
|
SQL 2000 Truncating 0's (zero) in Numeric column