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 |
|
jcb267
Constraint Violating Yak Guru
291 Posts |
 Posted - 2015-04-14 : 09:08:11
|
Hello - I have a character string for a zip code field. I am only looking for zip codes in the 01001 - 02791 range. Will where zip_code between '01001' and '02791' work on a character field? It seems to be dropping out some records but I cant find them.....Thanks! |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2015-04-14 : 09:23:18
|
| It should unless they were stored with a leading zero dropped.CREATE TABLE #Zips( ID int IDENTITY(1,1), ZIPCode Char(5) )INSERT INTO #ZipsVALUES('01001'),('02791'),('02792'),('02051'),('02027'),('01000'),('01309')SELECT * FROM #ZipsWHERE ZIPCode BETWEEN '01001' AND '02791' |
 |
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2015-04-14 : 09:29:09
|
| I just discovered that some had the leading zero dropped.... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-14 : 20:41:57
|
| Common issue when data is put into Excel for "handling purposes" . Excel chops off leading zeros (unless you carefully format the data as TEXT during import). We had a client who decided to use leading zeros on Product Codes ... turned out to be a bad choice as they were Excel fanatics (well ... not fanatical enough, as it turned out!) |
|
|
|
|
|
|
char format of zip code