Solved

Mixed Data - Need to pull Values from within the table

Posted on 2011-09-22
5
177 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now