By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,805 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

Using UPDATE to sequentially abbreviate address information

P: n/a
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_Fix
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_Fix
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_Fix
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_Fix
SET address_line_1_fix = REPLACE(address_line_1,'Boulevard','Blvd')
WHERE address_line_1 like '%Boulevard%'
GO
Jul 2 '08 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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_Fix
SET address_line_1_fix =
REPLACE(
REPLACE(
REPLACE(
REPLACE(address_line_1,
'Boulevard','Blvd'),
'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********@broadreachpartnersinc.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_Fix
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_Fix
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_Fix
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_Fix
SET address_line_1_fix = REPLACE(address_line_1,'Boulevard','Blvd')
WHERE address_line_1 like '%Boulevard%'
GO
Jul 2 '08 #2

P: n/a
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.
Jul 2 '08 #3

P: n/a
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_Fix
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_Fix
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_Fix
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_Fix
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_Fix
SET address_line_1_fix = REPLACE(address_line_1,'Boulevard','Blvd')
WHERE address_line_1 like '%Boulevard%'
GO
Jul 2 '08 #4

P: n/a
On Jul 2, 4:12*pm, "Roy Harvey (SQL Server MVP)" <roy_har...@snet.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_Fix
SET address_line_1_fix =
* * * *REPLACE(
* * * *REPLACE(
* * * *REPLACE(
* * * *REPLACE(address_line_1,
* * * * * * * *'Boulevard','Blvd'),
* * * * * * * *'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_Fix
SET address_line_1_fix = REPLACE(address_line_1_fix,'Street','St')
WHERE address_line_1_fix like '%Street%'
UPDATE dbo.All_Client_Companies_For_Fix
SET address_line_1_fix =
REPLACE(address_line_1_fix,'Boulevard','Blvd')
WHERE address_line_1_fix like '%Boulevard%'
GO
Jul 2 '08 #5

P: n/a
On Wed, 2 Jul 2008 13:21:46 -0700 (PDT), --CELKO--
<jc*******@earthlink.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
Jul 4 '08 #6

P: n/a
Iain Sharp wrote:
On Wed, 2 Jul 2008 13:21:46 -0700 (PDT), --CELKO--
<jc*******@earthlink.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.
Jul 4 '08 #7

P: n/a
On Fri, 04 Jul 2008 13:01:27 -0700, Ed Murphy <em*******@socal.rr.com>
wrote:
>Iain Sharp wrote:
>On Wed, 2 Jul 2008 13:21:46 -0700 (PDT), --CELKO--
<jc*******@earthlink.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
Jul 7 '08 #8

P: n/a
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
Jul 7 '08 #9

P: n/a
(jh******@googlemail.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****@sommarskog.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
Jul 7 '08 #10

P: n/a
Melissa Data has a free trial offer most of the time. I would also
read a freee copy of "Math, Myth & Magic of Name Search & Matching"

http://www.identitysystems.com/is_realbookform.html
Jul 8 '08 #11

This discussion thread is closed

Replies have been disabled for this discussion.