如何用SQL清理地址字段：移除多余的城市、州和邮政编码

我有一张包含address1、city、state、postal_code字段的表，部分address1字段中额外包含了城市、州和邮政编码（以逗号、空格或两者组合分隔）。示例：

Address1: 9999 western Rd, Los Angeles, CA, 90001
City: Los Angeles
State: CA
Postal: 90001
期望处理后Address1为: 9999 western Rd

我尝试了以下SQL语句修复地址（为简化假设所有字段非空，实际系统中无州的国家该字段为空或与国家名相同）：

SELECT LEFT(address1, PATINDEX('%[, ]'+city+'%', billingAddress) - 1)
FROM addresses
WHERE address1 like '%[, ]'+city+'%'+state+'%'+postal_code+'%'
    AND PATINDEX('%[, ]'+City+'%', address1) < 12

但存在问题：部分街道名称包含城市名，例如地址为9999 KIRKLAND WAY、城市为KIRKLAND时，执行该语句后街道名仅剩9999。请问如何用SQL解决此问题？

核心思路是从地址字符串的末尾反向匹配完整的城市-州-邮编组合，而非从开头定位城市名，以此避免街道名包含城市名时的误截断问题。

方法1：精准匹配末尾的地址后缀（适配SQL Server） #

针对逗号+空格、纯空格两种常见分隔方式，构造完整后缀模式，找到其起始位置后截取前方内容：

SELECT 
    CASE 
        -- 匹配逗号+空格分隔的后缀
        WHEN address1 LIKE '%' + city + ', ' + state + ', ' + postal_code
        THEN LEFT(address1, CHARINDEX(city + ', ' + state + ', ' + postal_code, address1) - 2)
        -- 匹配纯空格分隔的后缀
        WHEN address1 LIKE '%' + city + ' ' + state + ' ' + postal_code
        THEN LEFT(address1, CHARINDEX(city + ' ' + state + ' ' + postal_code, address1) - 1)
        -- 无匹配则保留原地址
        ELSE address1
    END AS cleaned_address1
FROM addresses
WHERE address1 LIKE '%' + city + '%' + state + '%' + postal_code + '%'

可根据实际数据的分隔规则，补充更多分隔符的匹配分支。

方法2：基于长度的反向截断（通用适配） #

若分隔符不固定，可先计算城市+州+邮编的总长度，验证地址末尾包含该组合后，直接从末尾截断：

SELECT 
    LEFT(address1, LEN(address1) - LEN(city + state + postal_code) - 4) AS cleaned_address1
    -- 减4是预留分隔符的冗余长度（如", , "这类多分隔符场景，可根据实际调整）
FROM addresses
WHERE 
    RIGHT(address1, LEN(city + state + postal_code) + 4) LIKE '%' + city + '%' + state + '%' + postal_code + '%'

方法3：单词边界匹配（避免部分匹配） #

如果你的SQL支持正则风格的单词边界（如SQL Server的[^a-zA-Z0-9]匹配非字母数字字符），可以通过限定城市名是独立单词来避免误匹配：

SELECT 
    LEFT(address1, PATINDEX('%[^a-zA-Z0-9]' + city + '[^a-zA-Z0-9]' + state + '[^a-zA-Z0-9]' + postal_code + '%', address1) - 1)
FROM addresses
WHERE 
    address1 LIKE '%[^a-zA-Z0-9]' + city + '[^a-zA-Z0-9]' + state + '[^a-zA-Z0-9]' + postal_code + '%'

这种方式能确保匹配到的城市名是独立的地址段，而非街道名称的一部分。

内容的提问来源于stack exchange，提问作者shano