Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Mixed Data - Need to pull Values from within the table

Posted on 2011-09-22
5
Medium Priority
?
185 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

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…
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

705 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