在 SQL Server 中匹配街道地址的方法有哪些? [英] What are ways to match street addresses in SQL Server?
问题描述
我们有一列街道地址:
枫树路123号
321 第一大道.
等等...
123 Maple Rd.
321 1st Ave.
etc...
有没有办法将这些地址与给定的输入相匹配?输入将是街道地址，但格式可能不同.例如:
Is there any way to match these addresses to a given input? The input would be a street address, but it might not be in the same format. For example:
枫树路123号
第一大道321号
123 Maple Road
321 1st Avenue
我们首先想到的是去除所有街道术语(rd、st、ave、blvd 等)的输入.
Our first thought is to strip the input of all street terms (rd, st, ave, blvd, etc).
显然，这不会一直可靠地匹配.还有其他方法可以尝试匹配 SQL Server 中的街道地址吗?
Obviously that won't match reliably all the time. Are there any other ways to try to match street addresses in SQL server?
我们可以使用用户定义的函数、存储过程和常规的旧 t-sql.我们不能使用 clr.
We can use user defined functions, stored procs and regular old t-sql. We cannot use clr.
推荐答案
为了进行正确的街道地址匹配，您需要将地址转换为标准化的形式.查看 USPS 邮政标准此处(我我假设您正在处理美国地址).如果您希望能够处理所有类型的美国邮件地址，这绝不是一个简单的过程.您可以使用 QAS 和 Satori Software 等公司提供的软件为您进行标准化.您需要导出地址，通过软件运行它们，然后将更新后的地址加载到数据库中.还有第三方供应商也将执行地址标准化.对于您正在尝试做的事情来说，这可能是矫枉过正，但这是最好的方法.如果您的数据库中的地址是标准化的，您将有更好的机会匹配它们(特别是如果您也可以标准化输入).
In order to do proper street address matching, you need to get your addresses into a standardized form. Have a look at the USPS postal standards here (I'm asssuming you're dealing with US addresses). It is by no means an easy process if you want to be able to deal with ALL types of US mail addresses. There is software available from companies like QAS and Satori Software that you can use to do the standardization for you. You'll need to export your addresses, run them through the software and then load the database with the updated addresses. There are also third party vendors that will perform the address standardization as well. It may be overkill for what you are trying to do but it's the best way to do it. if the addresses in your database are standardized you'll have a better chance of matching them (especially if you can standardize the input as well).
这篇关于在 SQL Server 中匹配街道地址的方法有哪些?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
