condense this working SQL into an algorithm

What is listed below works but I would like to condense it into a loop. Any
ideas? Thank you. -Greg
-- peform substring at these locations in column 1,5,9,13,17,21,25
CREATE TABLE #one
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #one
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #two
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #two
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 5, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #three
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #three
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 9, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #four
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #four
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 13, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #five
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #five
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 17, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #six
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #six
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 21, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #seven
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #seven
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 25, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE total
(AreaCode varchar(50),
TimeZone varchar(50))
insert into total
SELECT * FROM #one
UNION ALL
SELECT * FROM #two
UNION ALL
SELECT * FROM #three
UNION ALL
SELECT * FROM #four
UNION ALL
SELECT * FROM #five
UNION ALL
SELECT * FROM #six
UNION ALL
SELECT * FROM #sevenUntested:
INSERT total
SELECT DISTINCT SUBSTRING(AreaCode, a.Start * 4 + 1, 3)
, TimeZone
FROM ZipCodeDatabase_DELUXE, (SELECT 0 UNION SELECT 1 UNION SELECT 2
UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) a (Start)
-Alan|||Awesome Alan ! Works perfectly! Now I have to digest what you did along with
my lunch. ;-) -appreciatively -greg
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141750087.253473.75260@.z34g2000cwc.googlegroups.com...
> Untested:
> INSERT total
> SELECT DISTINCT SUBSTRING(AreaCode, a.Start * 4 + 1, 3)
> , TimeZone
> FROM ZipCodeDatabase_DELUXE, (SELECT 0 UNION SELECT 1 UNION SELECT 2
> UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) a (Start)
> -Alan
>|||well, first lets break down your results into a set of UNIONed queries:
INSERT total
SELECT SUBSTRING(AreaCode, 1, 3), TimeZone FROM ...
UNION
SELECT SUBSTRING(AreaCode, 1, 3), TimeZone FROM ...
Simple enough, right? Next, when you have multiple entities in your
FROM clause with no JOIN expression, SQL extrapolates all combinations.
SELECT *
FROM (SELECT 1 Number UNION SELECT 2) a
, (SELECT 'A' UNION SELECT 'B') b (Letter)
This is what I did, only I used a subquery that unioned the numbers 0
through 6. I recognized a linear pattern of your start position, so I
used that in the SUBSTRING function. While I could've not used that
formula and used the values 1, 5, 9, et cetera in my UNIONed list of
numbers, I thought it made things a little cleaner to use the formula
in the SUBSTRING function. The above query shows two ways of naming
your columns. I used the latter.
-Alan|||Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
instead of CHAR(3)' Likewise area_)code? etc.
Why do your tables all have no keys' Don't you know that there are no
loops in a declarative language? There are UNIONs in SQL and you can
use them to split up this mess. But why would have such poor data in
the first place?|||Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
instead of CHAR(3)' Likewise area_)code? etc.
Why do your tables all have no keys' Don't you know that there are no
loops in a declarative language? There are UNIONs in SQL and you can
use them to split up this mess. But why would have such poor data in
the first place?|||Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
instead of CHAR(3)' Likewise area_)code? etc.
Why do your tables all have no keys' Don't you know that there are no
loops in a declarative language? There are UNIONs in SQL and you can
use them to split up this mess. But why would have such poor data in
the first place?|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
> instead of CHAR(3)' Likewise area_)code? etc.
char(3) for a time zone? Need char(5), sign + four digits.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> har(3) for a time zone? Need char(5), sign + four digits. <<
That is a time displacement; a zone is "PST', etc. Also, I seem to
remember that we got rid of the "old fractional hours" displacements a
few yers ago, so you can use an integer off of UTC.|||--CELKO-- wrote:
> That is a time displacement; a zone is "PST', etc. Also, I seem to
> remember that we got rid of the "old fractional hours" displacements a
> few yers ago, so you can use an integer off of UTC.
Except there are still places in the world which are, for instance, 5
1/2 hours ahead of UTC (India)
Damien