Using UPDATE to sequentially abbreviate address information

[Chris H]

Greetings,

I'm trying to update an address field with "standard" abbreviations so
that I can do a comparison of various accounts to one another on the
address. I can update a set of records for "Road" to "Rd", but when I
tried to stack the update clauses, I seem to get random updates within
the file. All the updates are correct, but they're incomplete. Not
sure how this needs to be done, I added a TOP statement but that
didn't work. Is there way to simply string these together in a single
query?

The basic idea is to create the new address, "address_line_1 _fix",
while leaving the original address, "address_line_1 ", intact.

UPDATE TOP (100) PERCENT dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1,'Road', 'Rd')
WHERE address_line_1 like '%Road%'
GO
UPDATE TOP (100) PERCENT dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1,'Avenue ','Ave')
WHERE address_line_1 like '%Avenue%'
GO
UPDATE TOP (100) PERCENT dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1,'Street ','St')
WHERE address_line_1 like '%Street%'
GO
UPDATE TOP (100) PERCENT dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1,'Boulev ard','Blvd')
WHERE address_line_1 like '%Boulevard%'
GO

[Roy Harvey (SQL Server MVP)]

First of all get rid of the TOP (100) PERCENT nonsense. While it
should have no effect, it serves no purpose and just confuses things.

Second, if you are saying that not all rows you expect to be updated
are updated, turn your UPDATE commands into queries and see what is
returned. If the SELECT returns rows using a given WHERE clause, then
an UPDATE with the same WHERE clause should update the same rows. Also
double check the spelling of the literals; a different spelling of
'Boulevard' in the WHERE clause and SET clause would not work right.

If you want to do this in a single query you need to nest the REPLACE
functions, and OR the tests.

UPDATE dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix =
REPLACE(
REPLACE(
REPLACE(
REPLACE(address _line_1,
'Boulevard','Bl vd'),
'Street','St'),
'Avenue','Ave') ,
'Road','Rd')
WHERE (address_line_1 like '%Road%'
OR address_line_1 like '%Avenue%'
OR address_line_1 like '%Street%'
OR address_line_1 like '%Boulevard%')

Roy Harvey
Beacon Falls, CT

On Wed, 2 Jul 2008 12:26:57 -0700 (PDT), Chris H
<ch********@bro adreachpartners inc.comwrote:

>Greetings,

I'm trying to update an address field with "standard" abbreviations so
that I can do a comparison of various accounts to one another on the
address. I can update a set of records for "Road" to "Rd", but when I
tried to stack the update clauses, I seem to get random updates within
the file. All the updates are correct, but they're incomplete. Not
sure how this needs to be done, I added a TOP statement but that
didn't work. Is there way to simply string these together in a single
query?

The basic idea is to create the new address, "address_line_1 _fix",
while leaving the original address, "address_line_1 ", intact.

UPDATE TOP (100) PERCENT dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1,'Road', 'Rd')
WHERE address_line_1 like '%Road%'
GO
UPDATE TOP (100) PERCENT dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1,'Avenue ','Ave')
WHERE address_line_1 like '%Avenue%'
GO
UPDATE TOP (100) PERCENT dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1,'Street ','St')
WHERE address_line_1 like '%Street%'
GO
UPDATE TOP (100) PERCENT dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1,'Boulev ard','Blvd')
WHERE address_line_1 like '%Boulevard%'
GO

[--CELKO--]

There are address data scrubbing products from Melissa Data and Group
One which will do this for you and a lot more. Do not re-invent the
wheel.

[Gert-Jan Strik]

Please remove the silly TOP 100 PERCENT.

A potential problem with your replaces is that you are not using any
delimiter. If you address line reads "Broadway", then the "Road"-part
will be replaced with "Rd" resulting in "BRdway".

So you will need to figure out how to properly replace any individual
term, for example by prefixing and/or postfixing a space to both the
search term and the replacement term.

Once that is correct, you can simply nest several replacements into one
UPDATE statement. Something like this:

UPDATE dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(REPLACE (
address_line_1, 'Road', 'Rd')
, 'Street', 'St')
WHERE address_line_1 LIKE '%Road%'
OR address_line_1 LIKE '%Street%'

--
Gert-Jan
SQL Server MVP
Chris H wrote:

>
Greetings,

I'm trying to update an address field with "standard" abbreviations so
that I can do a comparison of various accounts to one another on the
address. I can update a set of records for "Road" to "Rd", but when I
tried to stack the update clauses, I seem to get random updates within
the file. All the updates are correct, but they're incomplete. Not
sure how this needs to be done, I added a TOP statement but that
didn't work. Is there way to simply string these together in a single
query?

The basic idea is to create the new address, "address_line_1 _fix",
while leaving the original address, "address_line_1 ", intact.

UPDATE TOP (100) PERCENT dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1,'Road', 'Rd')
WHERE address_line_1 like '%Road%'
GO
UPDATE TOP (100) PERCENT dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1,'Avenue ','Ave')
WHERE address_line_1 like '%Avenue%'
GO
UPDATE TOP (100) PERCENT dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1,'Street ','St')
WHERE address_line_1 like '%Street%'
GO
UPDATE TOP (100) PERCENT dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1,'Boulev ard','Blvd')
WHERE address_line_1 like '%Boulevard%'
GO

[Chris H]

On Jul 2, 4:12*pm, "Roy Harvey (SQL Server MVP)" <roy_har...@sne t.net>
wrote:

First of all get rid of the TOP (100) PERCENT nonsense. *While it
should have no effect, it serves no purpose and just confuses things.

Second, if you are saying that not all rows you expect to be updated
are updated, turn your UPDATE commands into queries and see what is
returned. *If the SELECT returns rows using a given WHERE clause, then
an UPDATE with the same WHERE clause should update the same rows. Also
double check the spelling of the literals; a different spelling of
'Boulevard' in the WHERE clause and SET clause would not work right.

If you want to do this in a single query you need to nest the REPLACE
functions, and OR the tests.

UPDATE dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix =
* * * *REPLACE(
* * * *REPLACE(
* * * *REPLACE(
* * * *REPLACE(addres s_line_1,
* * * * * * * *'Boulevard','B lvd'),
* * * * * * * *'Street','St') ,
* * * * * * * *'Avenue','Ave' ),
* * * * * * * *'Road','Rd')
WHERE (address_line_1 like '%Road%'
OR * * address_line_1 like '%Avenue%'
OR * * address_line_1 like '%Street%'
OR * * address_line_1 like '%Boulevard%')

Roy Harvey
Beacon Falls, CT

I started without the TOP clause but since didn't update, I tried it
as an option (no problem removing). When I execute the query, I get
reporting to the effect that there were updates applied. See below.
Which leads me to the solution that I just figured out while typing
this.... I'm replacing the subsequent updates from the original
Address (and undoing the previous statements).

(3597 row(s) affected)

(2970 row(s) affected)
.....
(95 row(s) affected)

(142 row(s) affected)

The fix was to move address_line_1_ fix (not - address_address _line_1)
into the replace clause:

UPDATE dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix = REPLACE(address _line_1_fix,'St reet','St')
WHERE address_line_1_ fix like '%Street%'
UPDATE dbo.All_Client_ Companies_For_F ix
SET address_line_1_ fix =
REPLACE(address _line_1_fix,'Bo ulevard','Blvd' )
WHERE address_line_1_ fix like '%Boulevard%'
GO

[Iain Sharp]

On Wed, 2 Jul 2008 13:21:46 -0700 (PDT), --CELKO--
<jc*******@eart hlink.netwrote:

>There are address data scrubbing products from Melissa Data and Group
One which will do this for you and a lot more. Do not re-invent the
wheel.

They look really useful. Please send me the money to buy them.

Iain

[Ed Murphy]

Iain Sharp wrote:

On Wed, 2 Jul 2008 13:21:46 -0700 (PDT), --CELKO--
<jc*******@eart hlink.netwrote:

>There are address data scrubbing products from Melissa Data and Group
One which will do this for you and a lot more. Do not re-invent the
wheel.

They look really useful. Please send me the money to buy them.

And how much money (or equivalent labor) were you going to spend on
rolling your own? I've never needed to do significant amounts of
address scrubbing, but if I did, I would certainly consider these
products likely to be a worthwhile investment.

[Iain Sharp]

On Fri, 04 Jul 2008 13:01:27 -0700, Ed Murphy <em*******@soca l.rr.com>
wrote:

>Iain Sharp wrote:

>On Wed, 2 Jul 2008 13:21:46 -0700 (PDT), --CELKO--
<jc*******@ear thlink.netwrote :

>>There are address data scrubbing products from Melissa Data and Group
One which will do this for you and a lot more. Do not re-invent the
wheel.

They look really useful. Please send me the money to buy them.

And how much money (or equivalent labor) were you going to spend on
rolling your own? I've never needed to do significant amounts of
address scrubbing, but if I did, I would certainly consider these
products likely to be a worthwhile investment.

Hmmm, about 15 minutes, at UKP11/hour = UKP2.75.

Iain

[jhofmeyr]

What is a UKP?

If you have only spent 15 mins scrubbing address data, you have been
exceptionally lucky to have only worked with unbelievably high quality
data.

J

[Erland Sommarskog]

(jh******@googl email.com) writes:

What is a UKP?

A currency that according to ISO 4217 is known as GBP. Or £ for short.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx