当前位置： 文江博客话题详情

SQL地址数据很乱，如何在查询中清理它？

发布于 2024-10-16 10:10:53 字数 2750 浏览 2 评论 0原文

我的地址数据存储在 SQL Server 2000 数据库中，我需要提取给定客户代码的所有地址。问题是，有很多拼写错误的地址，有些缺少部分等等。所以我需要以某种方式清理它。我需要剔除错误的拼写、缺失的部分等，并得出“平均”记录。例如，如果 New York 在 5 条记录中有 4 条拼写正确，则该值应该是返回的值。

我无法修改数据、验证输入数据或类似的事情。我只能修改数据的副本，或通过查询对其进行操作。

我在这里得到了部分答案存储在 SQL Server 中的地址有许多小的变化（错误），但我需要允许多个有效地址每个代码。

样本数据

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

所需输出

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
原文

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（6）

尽揽少女心 2024-10-23 10:10:53

最好的解决方案是使用 CASS 认证的地址标准化程序或服务来格式化和验证地址。除了拥有这方面工具的 USPS 之外，还有许多第三方程序或服务提供此功能。地址解析比您想象的要复杂得多，因此尝试发起一些查询来完成它将会充满危险。

Google 地理编码是另一个地方看看。。显然，谷歌要求您显示结果才能使用他们的地理编码服务。这就需要使用专用的地址解析器，例如 USPS 或第三方程序。

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.

回复 原文
鹤舞 2024-10-23 10:10:53

使用 group by 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

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
回复 原文
み青杉依旧 2024-10-23 10:10:53

基于您提到的相同答案，请尝试以下操作：

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

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
回复 原文
醉生梦死 2024-10-23 10:10:53

我们就从这里开始吧，哈哈……

-- 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  != '';

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  != '';
回复 原文
三月梨花 2024-10-23 10:10:53

为了工作，我帮助编写进行地址验证的软件（针对 SmartyStreets）。我想回应 Thomas 的回答，唯一实用且有效的解决方案是使用经过 CASS 认证的供应商。这是非常复杂的，但是这些服务会为您做这件事并且做得很好。

我还要补充一点，大多数免费 API 都有许可证限制，阻止使用其服务来处理地址列表（Google 不是唯一的 - 甚至 USPS 也对其 API 的使用有限制）。

我会推荐类似 LiveAddress 或 CASS 认证擦洗 满足您的需求（后者可能最适合现有的桌子），但我会让您自己进行研究，以便您了解更多信息。我很乐意亲自帮助您解决任何与地址相关的问题。

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.

回复 原文
无法回应 2024-10-23 10:10:53

OpenRefine 可能就是您的答案。

但是您需要导出到 csv，然后在 openrefine 中进行清理，然后将其导入回 SQL Server。

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.

回复 原文
~没有更多了~

关于作者

哭了丶谁疼

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

1CH1MKgiKxn9p

文章 0 评论 0

ゞ记忆︶ㄣ

文章 0 评论 0

JackDx

文章 0 评论 0

信远

文章 0 评论 0

yaoduoduo1995

文章 0 评论 0

霞映澄塘

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文