Using UPDATE to sequentially abbreviate address information
in [Using MS SQL Server]
|
From: Chris H on 2 Jul 2008 15:26 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
From: Roy Harvey (SQL Server MVP) on 2 Jul 2008 16:12 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 <chollstein(a)broadreachpartnersinc.com> 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
From: --CELKO-- on 2 Jul 2008 16:21 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.
From: Gert-Jan Strik on 2 Jul 2008 16:27 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
From: Chris H on 2 Jul 2008 16:54
On Jul 2, 4:12 pm, "Roy Harvey (SQL Server MVP)" <roy_har...(a)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 |