All geek questions in one place

The given SQL addresses are dirty, how to clear them in the query?

I have address data stored in sql server 2000 database and I need to pull out all the addresses for this client code. The problem is that there are many spelling addresses, some with missing parts, etc. So I need to clean them somehow. I need to get rid of misspellings, missing parts, etc. And come up with an “average” record. For example, if New York is correctly recorded in 4 out of 5 records, this should be a return value.

I can’t change the data, check it at the input or something like that. I can only modify a copy of the data or manipulate it with a query.

I received a partial answer here. The addresses stored on the SQL server have many small variations (errors) , but I need to resolve several valid addresses for each code.

Data examples

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 84,401 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
+3
5

CASS , . USPS, , , . , , , , , , .

Google Geocoding - .. , Google , , . , USPS .

+1

soundex(name), . , , . SQL Server 2000, , soundex.

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
+2

, ( SmartyStreets). , CASS . , .

, API , (Google ), USPS API).

I would recommend a service like LiveAddress or CASS-Certified Scrubbing for your needs (the latter is probably best suited for the existing spreadsheet), but I will let you do your own research so that you are more informed. I will be happy to help you personally with any questions related to the address.

+1
source

Based on the same answer you mentioned, try the following:

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
0
source

We will be here, 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  != '';
0
source

Source: https://habr.com/ru/post/1791078/

More articles: