TSQL 模糊地址匹配分组，2019 版

Question

我遇到这种情况，人们要求对错误的地址进行分组。我需要使用我拥有的工具/环境，我没有选择 Google API 或第三方数据科学工具。我也做了我的硬件，看到了几年前的帖子，所以仍然想检查所有是否有任何可用的更新。在我的场景中，人们希望将 ID 1-6 分组为单个，其余部分我添加了阴性测试。

SELECT * INTO #t FROM ( --test data:   select * from #t          drop table #t
    SELECT 1 Id,  '1 CROLANA HEIGHTS' Adr UNION   -- A vs O
    SELECT 2 Id, '1 CROLONA HEIGHTS' Adr union
    SELECT 3 Id, '1 CROLONA HEIGHT DRIVE' Adr union
    SELECT 4 Id,'1 CROLONA HEIGHTS DR' Adr union
    SELECT 5 Id, '1 CROLONA HGHTS DR' Adr union
    SELECT 6 Id, '1 CROLONA HTS DR' Adr  UNION
    ---------------------------------------- rest should not match
    SELECT 7 Id, '1 CORWING DR' Adr  UNION  
    SELECT 8 Id, '1 SUNNYHILL DRIVE' Adr  UNION
    SELECT 9 Id, '1 CROWN HILL DR' Adr  UNION
    SELECT 10 Id, '1 ADDISON DRv' Adr  ) a 
-------------------  and below is my fuzzy working script which can be improved)

SELECT  id,   adr, LEAD(adr,1) OVER ( ORDER BY adr ) adr_lead,
        SOUNDEX(adr) Sdx,  DIFFERENCE(adr, LEAD(adr,1) OVER ( ORDER BY adr )) diff
    ---     SOUNDEX(adr), COUNT(*) c
FROM #t 
--GROUP BY  SOUNDEX(adr)
WHERE SOUNDEX(adr) = SOUNDEX('1 CROLANA HEIGHTS')

Answer 1

我很乐意接受一些建议。我在字符串和独立单词的末尾使用 intell 替换来改善数据。

    DECLARE @st VARCHAR(100) = 'La_Beg_10 La_midleMacy La'  --replace et the end of string
SELECT 'ryba', @st, '-->' f, CASE WHEN @st LIKE '%' + ' La' 
      THEN SUBSTRING(@st,1,LEN(@st) - LEN('La')) + 'Lane' ELSE @st END N