one control loop with Substring to correct first normal column violation

given a table from an excel spreadsheet that contains a column that violates
first normal ;
Area Code TimeZone
787/939 4
212/646/718/917 5
I would like to create one procedure that loops through n possible values of
the area codes and creates a separate row for each value.
The Substring(s) below would have to start from positions
1,5,9,13,17,21,25,29 to capture the 3 character codes.
Right now I am manually creating each table with separate Substring
select/inserts with a union of all the tables and a select distinct.
The 65,000 rows of the original table end up deduped into about 245 rows of
distinct area codes per time zones.
Here are the first two sets of queries (there are 7 total) followed by the
consolidation with UNION ALL
-- create row with only first area code of '/' delimited values
CREATE TABLE first_areacode
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO first_areacode
(AreaCode, TimeZone)
select SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
from AreaCodeTimeZone
-- create row with only second area code of '/' delimited values
CREATE TABLE second_areacode
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO second_areacode
(AreaCode, TimeZone)
select SUBSTRING(AreaCode, 5, 3) AS AreaCode,TimeZone
from AreaCodeTimeZone
insert into combined_areacode
SELECT * FROM first_areacode
UNION ALL
SELECT * FROM second_areacode
UNION ALL
SELECT * FROM third_areacode
UNION ALL
etc.
.
Thank you for ideas to help automate this,
-Greghazz (hazz@.sonic.net) writes:
> I would like to create one procedure that loops through n possible
> values of the area codes and creates a separate row for each value. The
> Substring(s) below would have to start from positions
> 1,5,9,13,17,21,25,29 to capture the 3 character codes. Right now I am
> manually creating each table with separate Substring select/inserts with
> a union of all the tables and a select distinct. The 65,000 rows of the
> original table end up deduped into about 245 rows of distinct area codes
> per time zones.
> Here are the first two sets of queries (there are 7 total) followed by the
> consolidation with UNION ALL
> -- create row with only first area code of '/' delimited values
> CREATE TABLE first_areacode
> (AreaCode varchar(50),
> TimeZone varchar(50))
> INSERT INTO first_areacode
> (AreaCode, TimeZone)
> select SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
> from AreaCodeTimeZone
As long as you can rely on that the area codes are exact three
characters, no extra spaces etc, this could work:
SELECT substring(AreaCode, n, 3), TimeZone
FROM tbl
CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION ALL
SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
..) AS n
WHERE len(AreaCode) <= n
By the way, which version of SQL Server are you using?
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|||SQL 2005.
Thank you Erland !
-Greg
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns977F34D4AFD2Yazorman@.127.0.0.1...
> hazz (hazz@.sonic.net) writes:
> As long as you can rely on that the area codes are exact three
> characters, no extra spaces etc, this could work:
> SELECT substring(AreaCode, n, 3), TimeZone
> FROM tbl
> CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION
> ALL
> SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
> ...) AS n
> WHERE len(AreaCode) <= n
> By the way, which version of SQL Server are you using?
> --
> 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|||When I used this, the AreaCode column was blank although the TimeZone column
was populated.
I just reposted this with working sql that I would still like to condense
into an algorithm. Thanks, -Greg

> SELECT substring(AreaCode, n, 3), TimeZone
> FROM tbl
> CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION
> ALL
> SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
> ...) AS n
> WHERE len(AreaCode) <= n