如何在Snowflake中基于地址替换addresses表的错误country_code？

我拥有addresses和country_codes两张表，addresses表的country_code字段存在错误，需要根据address列（该列可能包含国家名称或国别码），用country_codes表中的正确country_code进行替换。

表结构及示例数据 #

addresses表 #

+--------------+-----------------------------------+
| country_code |              address              |
+--------------+-----------------------------------+
| US           | 1145 Oakmound Drive, US           |
| W            | 4733 Pallet Street, United States |
| F            | Rua Wanda Carnio 190, Brazil      |
| 22           | Via delle Viole 137, Italy        |
| 50           | 7 Essex Rd, GB                    |
+--------------+-----------------------------------+

country_codes表 #

+--------------+----------------+
| country_code |    country     |
+--------------+----------------+
| GB           | United Kingdom |
| BR           | Brazil         |
| IT           | Italy          |
| US           | United States  |
+--------------+----------------+

期望输出 #

+--------------+-----------------------------------+
| country_code |              address              |
+--------------+-----------------------------------+
| US           | 1145 Oakmound Drive, US           |
| US           | 4733 Pallet Street, United States |
| BR           | Rua Wanda Carnio 190, Brazil      |
| IT           | Via delle Viole 137, Italy        |
| GB           | 7 Essex Rd, GB                    |
+--------------+-----------------------------------+

解决方案 #

可以通过模糊匹配关联两张表，提取address列中包含的国家名称或代码，关联country_codes表获取正确的国家代码。以下是几种可行的实现方式：

方法1：使用LIKE进行基础模糊匹配 #

SELECT
    cc.country_code AS corrected_country_code,
    a.address
FROM addresses a
LEFT JOIN country_codes cc
    ON a.address LIKE '%' || cc.country || '%'
    OR a.address LIKE '%' || cc.country_code || '%'
ORDER BY a.address;

方法2：使用REGEXP_LIKE提升匹配精度 #

如果地址中的国家名称/代码可能和其他字符串混淆，用正则表达式匹配独立单词可以避免误匹配：

SELECT
    cc.country_code AS corrected_country_code,
    a.address
FROM addresses a
LEFT JOIN country_codes cc
    ON REGEXP_LIKE(a.address, '\\b' || cc.country || '\\b', 'i')
    OR REGEXP_LIKE(a.address, '\\b' || cc.country_code || '\\b', 'i')
ORDER BY a.address;

• \\b表示单词边界，确保匹配的是完整的国家名称/代码
• 'i'参数开启不区分大小写匹配，提升兼容性

方法3：直接更新addresses表的错误字段 #

如果需要直接修正addresses表中的country_code值，可使用UPDATE语句：

UPDATE addresses a
SET country_code = (
    SELECT cc.country_code
    FROM country_codes cc
    WHERE REGEXP_LIKE(a.address, '\\b' || cc.country || '\\b', 'i')
        OR REGEXP_LIKE(a.address, '\\b' || cc.country_code || '\\b', 'i')
);

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