Move suite # from address line 2 to add line 1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Move suite # from address line 2 to add line 1

Author

Topic

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-09-28 : 09:21:50

Hi,

I need to move suite number from address line 2 to address line 1, and keep street number on address line 2 (if there is a suite number in the mailing address). Do you have any coding that we can make it quick rather than manually fix each address.

I already ran a sql statement to search for those address that had SUITE NUMBER on address line 2, but don't know if we can have a way to move that suite # to address line 1, and move street number to address line 2.

FOR EXAMPLE:
ADDRESS LINE 1: 123 SUNOCO ST
ADDRESS LINE 2: SUITE 200
CITY: MIAMI
STATE: FL

Would like to make it like:
ADDRESS LINE 1: SUITE 200
ADDRESS LINE 2: 123 SUNOCO ST
CITY: MIAMI
STATE: FL

Any input would appreciate...thanks,

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-28 : 09:46:14

Table structure, sample data, etc., please

Jim

Everyday I learn something that somebody else already knew

smarty
Starting Member

13 Posts

Posted - 2009-09-28 : 09:46:46

You can do a update based on an inner join back onto the same table. Short form example below
UPDATE {YOUR_TABLE}
SET {Address line 1 column} = T.{Extracted suite number from address 2 column}
FROM {YOUR_TABLE} T

-----------------------------------
Free SQL server monitoring for DBA's
www.realsmartsoftware.co.uk

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-28 : 11:17:27

Try this

DECLARE @temp varchar (50)
DECLARE @Table TABLE (id int,add1 varchar(50),add2 varchar(50),city varchar(50),state char(2),zipcode char(5))
INSERT INTO @Table
SELECT 1234567,' ABC','SUITE 100','MENOMONEE FALLS','WI','53051' UNION ALL
SELECT 1234568,' ABCD','SUITE 500','DULUTH','GA','30096' UNION ALL
SELECT 1234569,' DCFE','SUITE 140','AMBLER','PA','19002' UNION ALL
SELECT 1234570,' EDF','SUITE 314','KINGSTON','PA','18704' UNION ALL
SELECT 1234571,' FGH','SUITE 111C','ALLENTOWN','PA','18103' UNION ALL
SELECT 1234572,' IJK','SUITE 300','PHILADELPHIA','PA','19102'

UPDATE @TABLE
SET @temp = add1
,add1 = add2
,add2 = @temp

select * from @table

Jim

Everyday I learn something that somebody else already knew

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-09-28 : 11:59:11

Thanks for your input....I used your update function...and it worked well.

UPDATE table
SET AD_STREET_1 = AD_STREET_2,
AD_STREET_2 = AD_STREET_1
WHERE ad_street_2 like 'Suite%'

quote:
Originally posted by jimf

Try this

DECLARE @temp varchar (50)
DECLARE @Table TABLE (id int,add1 varchar(50),add2 varchar(50),city varchar(50),state char(2),zipcode char(5))
INSERT INTO @Table
SELECT 1234567,' ABC','SUITE 100','MENOMONEE FALLS','WI','53051' UNION ALL
SELECT 1234568,' ABCD','SUITE 500','DULUTH','GA','30096' UNION ALL
SELECT 1234569,' DCFE','SUITE 140','AMBLER','PA','19002' UNION ALL
SELECT 1234570,' EDF','SUITE 314','KINGSTON','PA','18704' UNION ALL
SELECT 1234571,' FGH','SUITE 111C','ALLENTOWN','PA','18103' UNION ALL
SELECT 1234572,' IJK','SUITE 300','PHILADELPHIA','PA','19102'

UPDATE @TABLE
SET @temp = add1
,add1 = add2
,add2 = @temp

select * from @table

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-28 : 12:13:05

Just run the update statement. I inserted the records in to a table variable because you didn't provide that, and I wanted to demonstrate that the solution works. You may also want to verify that the solution works on your data before running it.

Jim

Everyday I learn something that somebody else already knew

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-09-28 : 12:20:45

Yes, I figured out after I sent previous note...but I used your update funtion...and it worked very well...that saved me a lot of time...:-)

Thanks a bunch!

quote:
Originally posted by jimf

Just run the update statement. I inserted the records in to a table variable because you didn't provide that, and I wanted to demonstrate that the solution works. You may also want to verify that the solution works on your data before running it.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources