August 21, 2009 at 9:18 am
I have a list stored in sql table in this format.
1 99991, 99992, 99993, 99994, 99995
2 99999, 99981, 99982, 99983, 99984, 99945, 65455
Now how would I convert this table to something like this.
ID Zipcode
1 99991
1 99992
1 99993
1 99994
1 99995
and so on.
Thanks in advance.
August 21, 2009 at 9:42 am
set nocount on;
declare @String varchar(max);
select substring(@String + ',', Number, charindex(',', @String + ',', Number) - Number)
from dbo.Numbers
where Number <= len(@String)
and substring(',' + @String, Number, 1) = ','
order by Number;
That's a sample string parser.
It requires a Numbers table, which can be created like this:
create table dbo.Numbers (
Number int primary key);
go
insert into dbo.Numbers (Number)
select top 10000 row_number() over (order by t1.object_id)
from sys.all_objects t1
cross join sys.all_objects t2;
If you search for "string parser" on this site, you'll find a few other versions and some articles about how they work. There's also a good article on http://www.simple-talk.com about "Helper Tables", which cover the subject, and also a few other uses for Numbers tables. Jeff Moden has an article on this site that calls it a "Tally" table, and Joe Celko apparently refers to it as a "Sequence" table. You can search those too. Very useful information and very educational.
- 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
August 21, 2009 at 12:42 pm
Thanks GSquared. The only question is how would i handle for cases when a row has one zipcode. So it will be something like
ID ZipCode
1 51254
Would the above work?
August 21, 2009 at 12:45 pm
Yep.
- 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
August 21, 2009 at 1:15 pm
Thanks GSquared. Awesome!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply