Solved

Mixed Data - Need to pull Values from within the table

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
(sql serv16)ssis 2016 question/check 1 127
shrink table after huge delete 2 34
the way to learn Microsoft BI 13 70
Set a variable value in SQL Procedure 3 26
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 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