SQL地址数据很乱，如何在查询中清理它？
我将地址数据存储在sql server 2000数据库中，我需要提取给定客户代码的所有地址。 问题是，有很多拼写错误的地址，一些缺少部分等，所以我需要以某种方式清理它。 我需要除去不好的拼写，缺少的部分等，并拿出“平均”记录。 例如，如果纽约在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
最好的解决方案是使用CASS认证的地址标准化程序或服务来格式化和验证地址。 除了拥有此工具的USPS之外，还有许多第三方程序或服务提供此功能。 地址解析比你想象的要复杂得多，因此试图掀起一些查询来解决这个问题将会充满危险。
谷歌的地理编码是另一个地方看。显然谷歌要求你显示结果使用他们的地理编码服务。 这留下了使用像USPS或第三方程序这样的专用地址解析器。
使用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
为了工作，我帮助编写确认地址验证的软件（对于SmartyStreets）。 我想回应托马斯的答案，唯一实用而有效的解决方案是使用CASS认证的供应商。 这是非常复杂的，但这些服务将为你做并做得很好。
我还要补充一点，大多数免费API都有许可限制，阻止他们使用他们的服务来处理地址列表（Google不是唯一的 - 即使USPS对他们的API使用有限制）。
我会推荐像LiveAddress或CASS认证的Scrubbing这样的服务来满足您的需求（后者可能适用于现有的表格），但我会让您自己做一些研究，以便让您了解更多信息。 我很乐意为您解决与地址相关的问题。
链接地址: http://www.djcxy.com/p/95689.html上一篇: SQL address data is messy, how to clean it up in a query?