SQL address data is messy, how to clean it up in a query?

Question

I have address data stored in an sql server 2000 database, and I need to pull out all the addresses for a given customer code. The problem is, there are a lot of misspelled addresses, some with missing parts, etc. So I need to clean this up somehow. I need to weed oout the bad spellings, missing parts, etc and come up with the "average" record. For example, if New York is spelled properly in 4 out of 5 records, that should be the value returned.

I can't modify the data, validate it on input, or anything like that. I can only modify a copy of the data, or manipulate it through a query.

I got a partial answer here Addresses stored in SQL server have many small variations(errors), but I need to allow for multiple valid addresses per code.

Sample Data

Code    Name                       Address1                      Address2           City            State          Zip     TimesUsed
10003   AMERICAN NUTRITON INC     2183 BALL STREET                                 OLDEN           Utah           87401     177
10003   AMEICAN NUTRITION INC     2183 BALL STREET              PO BOX 1504        OLDEN           Utah           87402     76
10003   AMERICAN NUTRITION INC    2183 BALL STREET                                 OLDEN           Utah           87402     24
10003   AMERICAN NUTRITION INC    2183 BALL STREET              PO BOX 1504        OLDEN           Utah           87402     17
10003   Samantha Brooks           506 S. Main Street                               Ellensburg      Washington     98296     1
10003   BEMIS COMPANY             1401 W. FOURTH PLAIN BLVD.                       VANCOUVER       Washington     98660     1
10003   CEI                       597 VANDYRE BOULEVARD                            WRIGHTSTOWN     Wisconsin      54180     1
10003   Pacific Pet               28th Avenue                                      OLDEN           Utah           84401     1
10003   PETSMART, INC.            16091 NORTH 25TH STREET                          PHOENA         Arizona        85027      1
10003   THE PET FIRM              16418 NORTH 37TH STREET                          PHOENA         Arizona        85503      1

Desired Output

Code    Name                      Address1                      Address2           City            State          Zip     
10003   AMERICAN NUTRITION INC    2183 BALL AVENUE                                 Olden           Utah           84401
10003   Samantha Brooks             506 S. Main Street                               Ellensburg      Washington     98296 
10003   BEMIS COMPANY             1401 W. FOURTH PLAIN BLVD.                       VANCOUVER       Washington     98660
10003   CEI                       975 VANDYKE ROAD                                 WRIGHTSTOWN     Wisconsin      54180
10003   Pacific Pet               29th Street                                      OGDEN           Utah           84401
10003   PETSMART, INC.            16091 NORTH 25TH AVENUE                          PHOENA         Arizona        85027
10003   THE PET FIRM              16418 NORTH 37TH STREET                          PHOENA         Arizona        85503

Ratty

If the intra-field differences are minor - a character or two, you could try grouping based on levenshtein distances, but getting things to group properly in the first place to calculate those distances basically puts you back to square one. – Italicize

Any solution to this would partly depend on the volume and frequency of the arriving data. Is it just 'new' data, or can existing items be updated? Does it matter if the sanitized address list you're trying to retrieve is not based on an up-to-the-minute copy of the garbage data? – Compton

@Marc - are you sure you want to even consider levenshtein distance? The Pet Shop and The Pot Shop is probably the shortest distance you can get without being equal, and they would be different companies. – Argenteuil

Dunno... Dogs that get high sound like they'd fun to take for a walk. But any kind of automated matching thing is going to screw up on something. Either you put in the time and fix up the data by hand, or you accept there's going to be oddities – Italicize

I looked at levenshtein, but I don't think I can use it here. For one thing it would be an absolute nightmere to convert to SQL, and also the best case wouldn't account for enough of my challenges. – Ratty

@Ratty - Standardizing street addresses will be a nightmare if you try to do it yourself. For example, take the first address. You might have one entry that is (2813 WALL AVENUE PO BOX 1234, NULL) (for Address1, Address), another that is (2813 WALL AVE. BOX 1234, NULL), another that is (2813 WALL AVE, #1234), another that is (2813 WALL AVENUE, PO BOX 1234). There are just too many variations. You invite Cthulhu if you try to write something yourself especially since you cannot correct the original source. – Franconia

Please tell me you didn't post real names and addresses. If these are real addresses, you should edit them so that the companies and individuals are not identifiable. – Engeddi

Answer 1

The best solution is to use a CASS certified address standardization program or service that will format and validate the address. Beyond the USPS which has tools for this, there are many third-party programs or services which provide this functionality. Address parsing is far more complicated than you might imagine and thus trying whip up a few queries to do it will be fraught with peril.

Google's Geocoding is another place to look.. Apparently Google requires you display the results to use their Geocoding service. That leaves using dedicated address parsers like the USPS or a third-party program.

Franconia

Unfourtunately, Google Geocoding is not an option due to this line from their website "geocoding results without displaying them on a map is prohibited". – Ratty

IANAL. However, I believe that yahoo's placefinder API provides similar functionality and their terms of use seem less limiting. There are also other 3rd party placefinders that charge a small fee where you would not be limited on how to use the data. developer.yahoo.com/geo/placefinder – Hengel

I'm going to use the yahoo placefinder api to check each address, and then save the valid addresses into a new database table. Then I'll provide that table. – Ratty

@thomas can you please say again why google wont be the right solution fo rthis? – Aguila

@I__. As MAW74656 mentioned, you need to read the Google Geocoding API terms of use: "the Geocoding API may only be used in conjunction with a Google map; geocoding results without displaying them on a map is prohibited." code.google.com/apis/maps/documentation/geocoding – Franconia

Answer 2

Using group by soundex(name) you will get result like this. You have to test on your data to figure out if this is helpful in your situation or not. I can not test this on SQL Server 2000 so I am not sure if soundex is available.

declare @T table (Code char(5), Name varchar(50), Address1 varchar(50))
insert into @T values
('10003', 'AMERICAN NUTRITON INC',  '2183 BALL STREET'),
('10003', 'AMEICAN NUTRITION INC',  '2183 BALL STREET'),
('10003', 'AMERICAN NUTRITION INC', '2183 BALL STREET'),
('10003', 'AMERICAN NUTRITION INC', '2183 BALL STREET'),
('10003', 'Samantha Brooks',        '506 S. Main Street'),
('10003', 'BEMIS COMPANY',          '1401 W. FOURTH PLAIN BLVD.'),
('10003', 'CEI',                    '597 VANDYRE BOULEVARD'),
('10003', 'Pacific Pet',            '28th Avenue'),
('10003', 'PETSMART, INC.',         '16091 NORTH 25TH STREET'),
('10003', 'THE PET FIRM',           '16418 NORTH 37TH STREET')

select
  min(Code) as Code,
  min(Name) as Name,
  min(Address1) as Address1
from @T
group by soundex(Name)
________________________________________________________
Code  Name                    Address1
10003 AMEICAN NUTRITION INC   2183 BALL STREET
10003 AMERICAN NUTRITION INC  2183 BALL STREET
10003 BEMIS COMPANY           1401 W. FOURTH PLAIN BLVD.
10003 CEI                     597 VANDYRE BOULEVARD
10003 Pacific Pet             28th Avenue
10003 PETSMART, INC.          16091 NORTH 25TH STREET
10003 Samantha Brooks         506 S. Main Street
10003 THE PET FIRM            16418 NORTH 37TH STREET

Abnormity

What if the are multiple spellings of American? – Ratty

@Ratty - In your sample data it was not. Now there is because the question is edited. Using group by solved the original question. – Abnormity

@Ratty - Have you considered soundex? Try to do a group by soundex(Name) instead of group by Name. – Abnormity

I'm not familiar with soundex, what is it? Yes, I edited the question because I realized after the fact it was missing. My edit more closely reflects the entire dataset. – Ratty

@Ratty - You can read about soundex here en.wikipedia.org/wiki/Soundex and about soundex in SQL Server here msdn.microsoft.com/en-us/library/ms187384.aspx. I do not have SQL Server 2000 so I can not check if soundex is available for you. – Abnormity

Answer 3

Base on the same answer you mentioned, try this:

select Code,
       (select top 1 Name from Table1 where Code=X.Code  and Address1=X.Address1
       group by Name order by count(*) desc) Name,
       Address1
from    Table1 X
group by Code,Address1

Rodomontade

Answer 4

For work, I help write software that does address verification (for SmartyStreets). I'd like to echo Thomas' answer in that the only practical and effective solution would be to use a CASS-Certified vendor. It is highly complicated, but those services will do it for you and do it well.

I'll also add that most free APIs have license restrictions that prevent the use of their service for processing lists of addresses (Google isn't the only one -- even the USPS has restrictions for use of their API).

I would recommend a service like LiveAddress or CASS-Certified Scrubbing for your needs (the latter probably best for an existing table), but I'll let you do your own research so you're more informed. I'll be happy to help you personally with any more address-related questions.

Durnan

Answer 5

We'll here is a start, Haha...

-- address, address2, city to uppercase
-- change all ".", "," to " " (space)
-- remove all double spaces from address (in case the previous call added double spaces)
-- trim
UPDATE
    consumers
SET
    address = TRIM(REPLACE(REPLACE(REPLACE(UPPER(address), '.', ' '), ',', ' '), '  ', ' ')),
    address2= TRIM(REPLACE(REPLACE(REPLACE(UPPER(address2), '.', ' '), ',', ' '), '  ', ' ')),
    city    = TRIM(REPLACE(REPLACE(REPLACE(UPPER(city), '.', ' '), ',', ' '), '  ', ' ')),
    state   = TRIM(UPPER(state))
WHERE
    id = _consumer_id;

-- address update all to use abbreviations Street -> St
UPDATE
    consumers
SET
    address = REPLACE(address, 'NORTH','N'),
    address = REPLACE(address, 'EAST','E'),
    address = REPLACE(address, 'SOUTH','S'),
    address = REPLACE(address, 'WEST','W'),

    address = REPLACE(address, 'ALLEY','ALY'),
    address = REPLACE(address, 'ANNEX','ANX'),
    address = REPLACE(address, 'APARTMENT','APT'),
    address = REPLACE(address, 'ARCADE','ARC'),
    address = REPLACE(address, 'AVENUE','AVE'),
    address = REPLACE(address, 'BASEMENT','BSMT'),
    address = REPLACE(address, 'BAYOU','BYU'),
    address = REPLACE(address, 'BEACH','BCH'),
    address = REPLACE(address, 'BEND','BND'),
    address = REPLACE(address, 'BLUFF','BLF'),
    address = REPLACE(address, 'BOTTOM','BTM'),
    address = REPLACE(address, 'BOULEVARD','BLVD'),
    address = REPLACE(address, 'BRANCH','BR'),
    address = REPLACE(address, 'BRIDGE','BRG'),
    address = REPLACE(address, 'BROOK','BRK'),
    address = REPLACE(address, 'BUILDING','BLDG'),
    address = REPLACE(address, 'BURG','BG'),
    address = REPLACE(address, 'BYPASS','BYP'),
    address = REPLACE(address, 'CAMP','CP'),
    address = REPLACE(address, 'CANYON','CYN'),
    address = REPLACE(address, 'CAPE','CPE'),
    address = REPLACE(address, 'CAUSEWAY','CSWY'),
    address = REPLACE(address, 'CENTER','CTR'),
    address = REPLACE(address, 'CIRCLE','CIR'),
    address = REPLACE(address, 'CLIFF','CLFS'),
    address = REPLACE(address, 'CLIFFS','CLFS'),
    address = REPLACE(address, 'CLUB','CLB'),
    address = REPLACE(address, 'CORNER','COR'),
    address = REPLACE(address, 'CORNERS','CORS'),
    address = REPLACE(address, 'COURSE','CRSE'),
    address = REPLACE(address, 'COURT','CT'),
    address = REPLACE(address, 'COURTS','CTS'),
    address = REPLACE(address, 'COVE','CV'),
    address = REPLACE(address, 'CREEK','CRK'),
    address = REPLACE(address, 'CRESCENT','CRES'),
    address = REPLACE(address, 'CROSSING','XING'),
    address = REPLACE(address, 'DALE','DL'),
    address = REPLACE(address, 'DAM','DM'),
    address = REPLACE(address, 'DEPARTMENT','DEPT'),
    address = REPLACE(address, 'DIVIDE','DV'),
    address = REPLACE(address, 'DRIVE','DR'),
    address = REPLACE(address, 'ESTATE','EST'),
    address = REPLACE(address, 'EXPRESSWAY','EXPY'),
    address = REPLACE(address, 'EXTENSION','EXT'),
    address = REPLACE(address, 'FALLS','FLS'),
    address = REPLACE(address, 'FERRY','FRY'),
    address = REPLACE(address, 'FIELD','FLD'),
    address = REPLACE(address, 'FIELDS','FLDS'),
    address = REPLACE(address, 'FLAT','FLT'),
    address = REPLACE(address, 'FLOOR','FL'),
    address = REPLACE(address, 'FORD','FRD'),
    address = REPLACE(address, 'FOREST','FRST'),
    address = REPLACE(address, 'FORGE','FRG'),
    address = REPLACE(address, 'FORK','FRK'),
    address = REPLACE(address, 'FORKS','FRKS'),
    address = REPLACE(address, 'FORT','FT'),
    address = REPLACE(address, 'FREEWAY','FWY'),
    address = REPLACE(address, 'FRONT','FRNT'),
    address = REPLACE(address, 'GARDEN','GDNS'),
    address = REPLACE(address, 'GARDENS','GDNS'),
    address = REPLACE(address, 'GATEWAY','GTWY'),
    address = REPLACE(address, 'GLEN','GLN'),
    address = REPLACE(address, 'GREEN','GRN'),
    address = REPLACE(address, 'GROVE','GRV'),
    address = REPLACE(address, 'HANGER','HNGR'),
    address = REPLACE(address, 'HARBOR','HBR'),
    address = REPLACE(address, 'HAVEN','HVN'),
    address = REPLACE(address, 'HEIGHTS','HTS'),
    address = REPLACE(address, 'HIGHWAY','HWY'),
    address = REPLACE(address, 'HILL','HL'),
    address = REPLACE(address, 'HILLS','HLS'),
    address = REPLACE(address, 'HOLLOW','HOLW'),
    address = REPLACE(address, 'INLET','INLT'),
    address = REPLACE(address, 'ISLAND','IS'),
    address = REPLACE(address, 'ISLANDS','ISS'),
    address = REPLACE(address, 'JUNCTION','JCT'),
    address = REPLACE(address, 'KEY','KY'),
    address = REPLACE(address, 'KNOLL','KNLS'),
    address = REPLACE(address, 'KNOLLS','KNLS'),
    address = REPLACE(address, 'LAKE','LK'),
    address = REPLACE(address, 'LAKES','LKS'),
    address = REPLACE(address, 'LANDING','LNDG'),
    address = REPLACE(address, 'LANE','LN'),
    address = REPLACE(address, 'LIGHT','LGT'),
    address = REPLACE(address, 'LOAF','LF'),
    address = REPLACE(address, 'LOBBY','LBBY'),
    address = REPLACE(address, 'LOCK','LCKS'),
    address = REPLACE(address, 'LOCKS','LCKS'),
    address = REPLACE(address, 'LODGE','LDG'),
    address = REPLACE(address, 'LOWER','LOWR'),
    address = REPLACE(address, 'MANOR','MNR'),
    address = REPLACE(address, 'MEADOW','MDWS'),
    address = REPLACE(address, 'MEADOWS','MDWS'),
    address = REPLACE(address, 'MILL','ML'),
    address = REPLACE(address, 'MILLS','MLS'),
    address = REPLACE(address, 'MISSION','MSN'),
    address = REPLACE(address, 'MOUNT','MT'),
    address = REPLACE(address, 'MOUNTAIN','MTN'),
    address = REPLACE(address, 'NECK','NCK'),
    address = REPLACE(address, 'OFFICE','OFC'),
    address = REPLACE(address, 'ORCHARD','ORCH'),
    address = REPLACE(address, 'PARKWAY','PKWY'),
    address = REPLACE(address, 'PENTHOUSE','PH'),
    address = REPLACE(address, 'PINE','PNES'),
    address = REPLACE(address, 'PINES','PNES'),
    address = REPLACE(address, 'PLACE','PL'),
    address = REPLACE(address, 'PLAIN','PLN'),
    address = REPLACE(address, 'PLAINS','PLNS'),
    address = REPLACE(address, 'PLAZA','PLZ'),
    address = REPLACE(address, 'POINT','PT'),
    address = REPLACE(address, 'PORT','PRT'),
    address = REPLACE(address, 'PRAIRIE','PR'),
    address = REPLACE(address, 'RADIAL','RADL'),
    address = REPLACE(address, 'RANCH','RNCH'),
    address = REPLACE(address, 'RAPID','RPDS'),
    address = REPLACE(address, 'RAPIDS','RPDS'),
    address = REPLACE(address, 'REST','RST'),
    address = REPLACE(address, 'RIDGE','RDG'),
    address = REPLACE(address, 'RIVER','RIV'),
    address = REPLACE(address, 'ROAD','RD'),
    address = REPLACE(address, 'ROOM','RM'),
    address = REPLACE(address, 'SHOAL','SHL'),
    address = REPLACE(address, 'SHOALS','SHLS'),
    address = REPLACE(address, 'SHORE','SHR'),
    address = REPLACE(address, 'SHORES','SHRS'),
    address = REPLACE(address, 'SPACE','SPC'),
    address = REPLACE(address, 'SPRING','SPG'),
    address = REPLACE(address, 'SPRINGS','SPGS'),
    address = REPLACE(address, 'SQUARE','SQ'),
    address = REPLACE(address, 'STATION','STA'),
    address = REPLACE(address, 'STRAVENUE','STRA'),
    address = REPLACE(address, 'STREAM','STRM'),
    address = REPLACE(address, 'STREET','ST'),
    address = REPLACE(address, 'SUITE','STE'),
    address = REPLACE(address, 'SUMMIT','SMT'),
    address = REPLACE(address, 'TERRACE','TER'),
    address = REPLACE(address, 'TRACE','TRCE'),
    address = REPLACE(address, 'TRACK','TRAK'),
    address = REPLACE(address, 'TRAFFICWAY','TRFY'),
    address = REPLACE(address, 'TRAIL','TRL'),
    address = REPLACE(address, 'TRAILER','TRLR'),
    address = REPLACE(address, 'TUNNEL','TUNL'),
    address = REPLACE(address, 'TURNPIKE','TPKE'),
    address = REPLACE(address, 'UNION','UN'),
    address = REPLACE(address, 'UPPER','UPPR'),
    address = REPLACE(address, 'VALLEY','VLY'),
    address = REPLACE(address, 'VIADUCT','VIA'),
    address = REPLACE(address, 'VIEW','VW'),
    address = REPLACE(address, 'VILLAGE','VLG'),
    address = REPLACE(address, 'VILLE','VL'),
    address = REPLACE(address, 'VISTA','VIS'),
    address = REPLACE(address, 'WAY','WAY'),
    address = REPLACE(address, 'WELL','WLS'),
    address = REPLACE(address, 'WELLS','WLS')
WHERE
    id = _consumer_id;


-- do the same for address2 but only if it has a value ( might help with speed? )
UPDATE
    consumers
SET
    address2 = REPLACE(address2, 'NORTH','N'),
    address2 = REPLACE(address2, 'EAST','E'),
    address2 = REPLACE(address2, 'SOUTH','S'),
    address2 = REPLACE(address2, 'WEST','W'),

    address2 = REPLACE(address2, 'ALLEY','ALY'),
    address2 = REPLACE(address2, 'ANNEX','ANX'),
    address2 = REPLACE(address2, 'APARTMENT','APT'),
    address2 = REPLACE(address2, 'ARCADE','ARC'),
    address2 = REPLACE(address2, 'AVENUE','AVE'),
    address2 = REPLACE(address2, 'BASEMENT','BSMT'),
    address2 = REPLACE(address2, 'BAYOU','BYU'),
    address2 = REPLACE(address2, 'BEACH','BCH'),
    address2 = REPLACE(address2, 'BEND','BND'),
    address2 = REPLACE(address2, 'BLUFF','BLF'),
    address2 = REPLACE(address2, 'BOTTOM','BTM'),
    address2 = REPLACE(address2, 'BOULEVARD','BLVD'),
    address2 = REPLACE(address2, 'BRANCH','BR'),
    address2 = REPLACE(address2, 'BRIDGE','BRG'),
    address2 = REPLACE(address2, 'BROOK','BRK'),
    address2 = REPLACE(address2, 'BUILDING','BLDG'),
    address2 = REPLACE(address2, 'BURG','BG'),
    address2 = REPLACE(address2, 'BYPASS','BYP'),
    address2 = REPLACE(address2, 'CAMP','CP'),
    address2 = REPLACE(address2, 'CANYON','CYN'),
    address2 = REPLACE(address2, 'CAPE','CPE'),
    address2 = REPLACE(address2, 'CAUSEWAY','CSWY'),
    address2 = REPLACE(address2, 'CENTER','CTR'),
    address2 = REPLACE(address2, 'CIRCLE','CIR'),
    address2 = REPLACE(address2, 'CLIFF','CLFS'),
    address2 = REPLACE(address2, 'CLIFFS','CLFS'),
    address2 = REPLACE(address2, 'CLUB','CLB'),
    address2 = REPLACE(address2, 'CORNER','COR'),
    address2 = REPLACE(address2, 'CORNERS','CORS'),
    address2 = REPLACE(address2, 'COURSE','CRSE'),
    address2 = REPLACE(address2, 'COURT','CT'),
    address2 = REPLACE(address2, 'COURTS','CTS'),
    address2 = REPLACE(address2, 'COVE','CV'),
    address2 = REPLACE(address2, 'CREEK','CRK'),
    address2 = REPLACE(address2, 'CRESCENT','CRES'),
    address2 = REPLACE(address2, 'CROSSING','XING'),
    address2 = REPLACE(address2, 'DALE','DL'),
    address2 = REPLACE(address2, 'DAM','DM'),
    address2 = REPLACE(address2, 'DEPARTMENT','DEPT'),
    address2 = REPLACE(address2, 'DIVIDE','DV'),
    address2 = REPLACE(address2, 'DRIVE','DR'),
    address2 = REPLACE(address2, 'ESTATE','EST'),
    address2 = REPLACE(address2, 'EXPRESSWAY','EXPY'),
    address2 = REPLACE(address2, 'EXTENSION','EXT'),
    address2 = REPLACE(address2, 'FALLS','FLS'),
    address2 = REPLACE(address2, 'FERRY','FRY'),
    address2 = REPLACE(address2, 'FIELD','FLD'),
    address2 = REPLACE(address2, 'FIELDS','FLDS'),
    address2 = REPLACE(address2, 'FLAT','FLT'),
    address2 = REPLACE(address2, 'FLOOR','FL'),
    address2 = REPLACE(address2, 'FORD','FRD'),
    address2 = REPLACE(address2, 'FOREST','FRST'),
    address2 = REPLACE(address2, 'FORGE','FRG'),
    address2 = REPLACE(address2, 'FORK','FRK'),
    address2 = REPLACE(address2, 'FORKS','FRKS'),
    address2 = REPLACE(address2, 'FORT','FT'),
    address2 = REPLACE(address2, 'FREEWAY','FWY'),
    address2 = REPLACE(address2, 'FRONT','FRNT'),
    address2 = REPLACE(address2, 'GARDEN','GDNS'),
    address2 = REPLACE(address2, 'GARDENS','GDNS'),
    address2 = REPLACE(address2, 'GATEWAY','GTWY'),
    address2 = REPLACE(address2, 'GLEN','GLN'),
    address2 = REPLACE(address2, 'GREEN','GRN'),
    address2 = REPLACE(address2, 'GROVE','GRV'),
    address2 = REPLACE(address2, 'HANGER','HNGR'),
    address2 = REPLACE(address2, 'HARBOR','HBR'),
    address2 = REPLACE(address2, 'HAVEN','HVN'),
    address2 = REPLACE(address2, 'HEIGHTS','HTS'),
    address2 = REPLACE(address2, 'HIGHWAY','HWY'),
    address2 = REPLACE(address2, 'HILL','HL'),
    address2 = REPLACE(address2, 'HILLS','HLS'),
    address2 = REPLACE(address2, 'HOLLOW','HOLW'),
    address2 = REPLACE(address2, 'INLET','INLT'),
    address2 = REPLACE(address2, 'ISLAND','IS'),
    address2 = REPLACE(address2, 'ISLANDS','ISS'),
    address2 = REPLACE(address2, 'JUNCTION','JCT'),
    address2 = REPLACE(address2, 'KEY','KY'),
    address2 = REPLACE(address2, 'KNOLL','KNLS'),
    address2 = REPLACE(address2, 'KNOLLS','KNLS'),
    address2 = REPLACE(address2, 'LAKE','LK'),
    address2 = REPLACE(address2, 'LAKES','LKS'),
    address2 = REPLACE(address2, 'LANDING','LNDG'),
    address2 = REPLACE(address2, 'LANE','LN'),
    address2 = REPLACE(address2, 'LIGHT','LGT'),
    address2 = REPLACE(address2, 'LOAF','LF'),
    address2 = REPLACE(address2, 'LOBBY','LBBY'),
    address2 = REPLACE(address2, 'LOCK','LCKS'),
    address2 = REPLACE(address2, 'LOCKS','LCKS'),
    address2 = REPLACE(address2, 'LODGE','LDG'),
    address2 = REPLACE(address2, 'LOWER','LOWR'),
    address2 = REPLACE(address2, 'MANOR','MNR'),
    address2 = REPLACE(address2, 'MEADOW','MDWS'),
    address2 = REPLACE(address2, 'MEADOWS','MDWS'),
    address2 = REPLACE(address2, 'MILL','ML'),
    address2 = REPLACE(address2, 'MILLS','MLS'),
    address2 = REPLACE(address2, 'MISSION','MSN'),
    address2 = REPLACE(address2, 'MOUNT','MT'),
    address2 = REPLACE(address2, 'MOUNTAIN','MTN'),
    address2 = REPLACE(address2, 'NECK','NCK'),
    address2 = REPLACE(address2, 'OFFICE','OFC'),
    address2 = REPLACE(address2, 'ORCHARD','ORCH'),
    address2 = REPLACE(address2, 'PARKWAY','PKWY'),
    address2 = REPLACE(address2, 'PENTHOUSE','PH'),
    address2 = REPLACE(address2, 'PINE','PNES'),
    address2 = REPLACE(address2, 'PINES','PNES'),
    address2 = REPLACE(address2, 'PLACE','PL'),
    address2 = REPLACE(address2, 'PLAIN','PLN'),
    address2 = REPLACE(address2, 'PLAINS','PLNS'),
    address2 = REPLACE(address2, 'PLAZA','PLZ'),
    address2 = REPLACE(address2, 'POINT','PT'),
    address2 = REPLACE(address2, 'PORT','PRT'),
    address2 = REPLACE(address2, 'PRAIRIE','PR'),
    address2 = REPLACE(address2, 'RADIAL','RADL'),
    address2 = REPLACE(address2, 'RANCH','RNCH'),
    address2 = REPLACE(address2, 'RAPID','RPDS'),
    address2 = REPLACE(address2, 'RAPIDS','RPDS'),
    address2 = REPLACE(address2, 'REST','RST'),
    address2 = REPLACE(address2, 'RIDGE','RDG'),
    address2 = REPLACE(address2, 'RIVER','RIV'),
    address2 = REPLACE(address2, 'ROAD','RD'),
    address2 = REPLACE(address2, 'ROOM','RM'),
    address2 = REPLACE(address2, 'SHOAL','SHL'),
    address2 = REPLACE(address2, 'SHOALS','SHLS'),
    address2 = REPLACE(address2, 'SHORE','SHR'),
    address2 = REPLACE(address2, 'SHORES','SHRS'),
    address2 = REPLACE(address2, 'SPACE','SPC'),
    address2 = REPLACE(address2, 'SPRING','SPG'),
    address2 = REPLACE(address2, 'SPRINGS','SPGS'),
    address2 = REPLACE(address2, 'SQUARE','SQ'),
    address2 = REPLACE(address2, 'STATION','STA'),
    address2 = REPLACE(address2, 'STRAVENUE','STRA'),
    address2 = REPLACE(address2, 'STREAM','STRM'),
    address2 = REPLACE(address2, 'STREET','ST'),
    address2 = REPLACE(address2, 'SUITE','STE'),
    address2 = REPLACE(address2, 'SUMMIT','SMT'),
    address2 = REPLACE(address2, 'TERRACE','TER'),
    address2 = REPLACE(address2, 'TRACE','TRCE'),
    address2 = REPLACE(address2, 'TRACK','TRAK'),
    address2 = REPLACE(address2, 'TRAFFICWAY','TRFY'),
    address2 = REPLACE(address2, 'TRAIL','TRL'),
    address2 = REPLACE(address2, 'TRAILER','TRLR'),
    address2 = REPLACE(address2, 'TUNNEL','TUNL'),
    address2 = REPLACE(address2, 'TURNPIKE','TPKE'),
    address2 = REPLACE(address2, 'UNION','UN'),
    address2 = REPLACE(address2, 'UPPER','UPPR'),
    address2 = REPLACE(address2, 'VALLEY','VLY'),
    address2 = REPLACE(address2, 'VIADUCT','VIA'),
    address2 = REPLACE(address2, 'VIEW','VW'),
    address2 = REPLACE(address2, 'VILLAGE','VLG'),
    address2 = REPLACE(address2, 'VILLE','VL'),
    address2 = REPLACE(address2, 'VISTA','VIS'),
    address2 = REPLACE(address2, 'WAY','WAY'),
    address2 = REPLACE(address2, 'WELL','WLS'),
    address2 = REPLACE(address2, 'WELLS','WLS')
WHERE
    id = _consumer_id
    AND address2  != '';

Acrylonitrile

FYI - Without checking for whitespace in front or behind, or validating that the token you're replacing is not part of a compound word, you risk destroying the data. For example, Grand Forks, Eastern Ave, Buena Vista. These would all be incorrect. – Penetrate

Answer 6

OpenRefine may be your answer.

But you need to export to csv and then cleanup in openrefine and the import it back to SQL Server.

Glyptodont