Trying to convert number range (1-5) to numerical progression (1,2,3,4,5)

Question

Post reply

Trying to convert number range (1-5) to numerical progression (1,2,3,4,5)

tony.hayes

SSC Veteran

Points: 236
More actions
January 23, 2009 at 4:32 pm

#155169

I have downloaded an array from the us postal service for zipcodes and where the first 3 digits are contiguous they have displayed them as a number range ie: 301-305. I would like to use the data in this array, but I need to convert these ranges back to a numerical progression, separated by commas ie: 301,302,303,304,305. Does anybody have an idea how I can do this in SQL 2005 via code? I have thought I might use some kind of substring command to set the beginning and ending values of a count, but I just can't wrap my head around it. Any help would be greatly appreciated. Thanks in advance.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

May seem like overhead, but Create a numbers table with sequential numbers in it. It becomes very useful over time.

Check out this article,

http://www.sqlservercentral.com/articles/Advanced+Querying/2547/

Along with other forum post which has a def for a function to get a # Range

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

Yep... a Numbers or Tally table would do it. You just need to write the simple logic to split dashed-pairs when they occur.

For an explanation of how a Numbers or Tally table actually works it's magic, take a peek at the following article...

[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font][/url]

... I'll be back in a minute...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

Here ya go, Tony... lemme know how this works for ya... compete with test code... of course, you need to create the Tally table first, but you've got a couple of good articles to tell you how to do that... 😉

--===== Create a test table to hold some zip code ranges.

-- Note that this is NOT a part of the solution

CREATE TABLE #ZipRange

(

ZipRange VARCHAR(7)

)

--===== Populate the test table with some sample data

-- Note that this is NOT a part of the solution

INSERT INTO #ZipRange

(ZipRange)

SELECT '028-128' UNION ALL

SELECT '300-305' UNION ALL

SELECT '307' UNION ALL

SELECT '309' UNION ALL

SELECT '320' UNION ALL

SELECT '333-400' UNION ALL

SELECT '410-420' UNION ALL

SELECT '423'

--===== Now, expand the zip code ranges.

-- This is easy because of the BETWEEN and the fact that

-- all the numbers are 3 characters

SELECT RIGHT('000'+CAST(t.N AS VARCHAR(3)),3) AS Zip3

FROM #ZipRange z

INNER JOIN dbo.Tally t

ON t.N BETWEEN LEFT(z.ZipRange,3) AND RIGHT(z.ZipRange,3)

ORDER BY t.N

Speaking of test code, take a look at the link in my signature to get better answers quicker. A bit of data like what I included in the test code make it real easy for folks to help you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tony.hayes SSC Veteran Points: 236 More actions · Answer 4

Sorry, first post ever. The only way I could get the data electronically was to copy from where USPS had posted to their website. An example of the zip code listings is below. I copied off their site, then dropped it into excel to break out the columns. As you can see they have run the numbers together, I will only need to run this a few times a year, but I was hoping I could feed the beginning number (117) and ending number (119) in as seperate variables, then output the numerical progression 117,118,119

Then move to the next row and do the same etc...

005, 117-119 AADC MID-ISLAND NY 117

006-009 AADC SAN JUAN PR 006

010-013 AADC SPRINGFIELD MA 010

014-017 AADC CENTRAL MA 015

025-029 AADC PROVIDENCE RI 028

030-034, 038, 039 AADC MANCHESTER NH 030

035-037, 050-054, 056-059AADC WHITE RIV JCT VT 050

040-049 AADC PORTLAND ME 040

060-062 AADC HARTFORD CT 060

Lowell SSC Guru Points: 323518 More actions · Answer 5

there was a different thread on zipcodes here where i posted a text file to a bunch of zip code/city/state stuff...are you just trying to get a list of all the zip codes and their city/states?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tony.hayes SSC Veteran Points: 236 More actions · Answer 6

No, I work for a Payroll company and several times of year we need to send out mass prints via Bulk mail (17-20,000 copies) The post office only allows bulk mail if their are 150 pieces or more per distibution center (AADC), so the array I posted is the first three digits of the zip codes associated with a distibution center (column 2). The goal is to have code that I can use during the print process to batch all mail items going to a distribution center for easy packaging, otherwise there will be a lot of manual sorting involved. Also some of the smaller zipcodes with less than 150 items can be combined to bring them up over 150 and allow for bulk mailing. I also want to be able to update this by feeding in the zipcode data from the USPS, should they change which zips the distribution centers handle.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

tony.hayes (1/23/2009)
Sorry, first post ever. The only way I could get the data electronically was to copy from where USPS had posted to their website. An example of the zip code listings is below. I copied off their site, then dropped it into excel to break out the columns. As you can see they have run the numbers together, I will only need to run this a few times a year, but I was hoping I could feed the beginning number (117) and ending number (119) in as seperate variables, then output the numerical progression 117,118,119
Then move to the next row and do the same etc...
005, 117-119 AADC MID-ISLAND NY 117
006-009 AADC SAN JUAN PR 006
010-013 AADC SPRINGFIELD MA 010
014-017 AADC CENTRAL MA 015
025-029 AADC PROVIDENCE RI 028
030-034, 038, 039 AADC MANCHESTER NH 030
035-037, 050-054, 056-059AADC WHITE RIV JCT VT 050
040-049 AADC PORTLAND ME 040
060-062 AADC HARTFORD CT 060

Please provide the URL for the USPS page you're talking about and identify the file you're downloading. No sense sifting through the manure to see what the horse was thinking... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tony.hayes SSC Veteran Points: 236 More actions · Answer 8

URL is here:

http://pe.usps.com/text/dmm300/L801.htm

Typical government thinking - they only provide an electronic format in pdf

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 9

tony.hayes (1/23/2009)
URL is here:
http://pe.usps.com/text/dmm300/L801.htm
Typical government thinking - they only provide an electronic format in pdf

Thanks, Tony... lemme see what I can do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tony.hayes SSC Veteran Points: 236 More actions · Answer 10

Thanks for taking all this time - I will start familiarizing myself with the Tally table code - this is the first I have heard of it. I could see a lot of uses for it however...

GSquared SSC Guru Points: 260824 More actions · Answer 11

You might want to look into something like Satori's BulkMailer (http://www.satorisoftware.com/Products/BulkMailer/default.aspx). It's a pre-packaged solution for CASS and presort, provides all the paperwork, etc. Not very expensive, either, for a single-seat license.

My prior two jobs, we used their Architect product (which is for large-scale mailing operations and list automation), and it was really, really a good product.

There are other products that do the same thing.

Might be easier, cheaper and more effective to either buy the software, or send your list to a company that can that service for you. Lots of companies that will presort for you for a very reasonable price.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

tony.hayes SSC Veteran Points: 236 More actions · Answer 12

If it was a simple mail merge that might be possible, but we produce custom legal certificates in house on z-fold self seal documents, as well as W2's etc... we wouldn't want to print them outside.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 13

tony.hayes (1/23/2009)
If it was a simple mail merge that might be possible, but we produce custom legal certificates in house on z-fold self seal documents, as well as W2's etc... we wouldn't want to print them outside.

Any CASS certification product will do this. If I remember correctly, a product called My Mailer worked great for me. Did the Z-Fold and the window envelopes and the whole 9 yards. But, I digress...

I've attached two files... the .txt file contains the SQL to do everything except make the Tally table. It also contains the SELECT statements that were built by the attached spreadsheet. Take a look at the formula in the spreadsheet to see how easy this will be to do in the future. It's not 100% automatic, but it's a lot quicker that what you've been doing.

Lemme know how it works out for you. It was a fun exercise... thanks for posting it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tony.hayes SSC Veteran Points: 236 More actions · Answer 14

This looks like it will work out perfectly - I built a Tally table and prefilled it with numbers between 0 and 65535 and your code tied right in. Thanks again for taking the time - I am posting my Tally table code in case anyone else needs to have something similar:

USE model

GO

CREATE TABLE dbo.Tally(

Number INT NOT NULL,

CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (Number)

WITH FILLFACTOR = 100)

GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

INSERT INTO Tally

SELECT(a.Number * 256) + b.Number AS Number

FROM (SELECT numberFROM master..spt_valuesWHERE type = 'P' AND number <= 255) a (Number),

(SELECT numberFROM master..spt_valuesWHERE type = 'P'AND number <= 255) b (Number)

GO