Solved

Mixed Data - Need to pull Values from within the table

Posted on 2011-09-22
5
180 Views
Last Modified: 2012-05-12
Hi - We have a 7,000 page Excel Spreadsheet containing customer inforamtion that resides in one column.  I imported this into SQL Server 2008 and have run select/delete statments to get ride a bunch of garbage.  Now, I wish to pull only address information but need to see if anyone has any idea on how I can script this or if there is a free-ware out there that can pull data.  

I have attached a sample bogus listing of the table to show what kind of stuff is mixed in.  I need the
Customer's Name
Street Name
City, State, Zip

Nothing else.  Common point is the USA at the end of the address statement.
Ideally, if I could write a script that would loop and essentially say:  Every time USA is found, capture the 3 lines directly preceeding the row with USA displayed....
 
I thought 3 lines preceeding as Apartments etc are listed on line 3 of the groupings.
Line 1 = Name
Line 2 = Street (PO Box0
Line 3 = Apartment OR is the City State, zip USA row.

Help Please.

I am using SQL Server 2008 R2


AAAA, DONALD O 
1244 A STREET  
SOME CITY, AZ 12345 USA 
10867 125-55-9999 
AAA, BUFFY  01/07/1999 Child BDO 
AAA, SLAYER O 02/13/1956 Self BDO 
AAAA, DONALD O 
1244 A STREET  
SOME CITY, AZ 12345 USA 
10867 125-55-9999 
AAA, BUFFY  01/07/1999 Child BDO 
AAA, SLAYER O 02/13/1956 Self BDO 
AAAA, DONALD O 
1244 A STREET  
SOME CITY, AZ 12345 USA 
10867 125-55-9999 
AAA, BUFFY  01/07/1999 Child BDO 
AAA, SLAYER O 02/13/1956 Self BDO 
AAAA, DONALD O 
1244 A STREET  
SOME CITY, AZ 12345 USA 
10867 125-55-9999 
AAA, BUFFY  01/07/1999 Child BDO 
AAA, SLAYER O 02/13/1956 Self BDO

Open in new window

0
Comment
Question by:SQLwatcher
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Wizilling
ID: 36583945
some scripting ideas:

create an identity column in your table. (if you dont have one)

script logic:

insert into #temp (ID )
select ID from your table where column1 like '%usa'


declare cursor
for select id from #temp

loop begin
     --- this will give you the 3 lines preceeding
      select column
     from yourtable
      where ID >= @id-2 and ID <= @id

end loop





0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36585861
Try this
alter table addr add Id int identity(1,1)
go
select * from addr where id in (
select rgbvalue from (
select id id3,id-1 id2,ID-2 id1 from addr tA 
where ta.data like '%USA%')p
unpivot
(rgbvalue for iid in (id3,id2,id1 ))
as unpvt)
go
alter table addr drop column id
go
select * from addr

Open in new window

0
 

Author Comment

by:SQLwatcher
ID: 36587515
This looks really good. Now. is there a way to insert a blank row after each USA value?  
0
 
LVL 9

Accepted Solution

by:
sachinpatil10d earned 500 total points
ID: 36597411
check below query for blank row
alter table addr add Id int identity(1,1)
go
select * from 
(select '' data,0 id union all
select * from addr 

)tt right outer join 
(select rgbvalue from (
select ID-2 id1,id-1 id2,id id3,0 id0 from addr tA 
where ta.data like '%USA%')p
unpivot
(rgbvalue for iid in (id1,id2,id3,id0 ))
as unpvt)t on tt.id = t.rgbvalue 
go
alter table addr drop column id
go
select * from addr

Open in new window

0
 

Author Closing Comment

by:SQLwatcher
ID: 36816882
Thank you both!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question