Oracle中对比格式不同的两列地址数据（缩写与全称差异）

针对两张表中地址因缩写（如SE、Ave、St）和全称（如Southeast、Avenue、Street）导致的匹配问题，可通过以下两种方案实现准确对比：

方案1：自定义地址标准化函数 #

创建PL/SQL函数将地址统一转换为标准格式（例如全部转为全称），之后直接对比转换结果即可。

示例函数 #

CREATE OR REPLACE FUNCTION normalize_address(p_address IN VARCHAR2) RETURN VARCHAR2 IS
    v_result VARCHAR2(1000);
BEGIN
    -- 统一转大写，消除大小写差异
    v_result := UPPER(p_address);
    
    -- 按从长到短顺序替换缩写为全称，避免部分匹配错误
    v_result := REGEXP_REPLACE(v_result, '\bSE\b', 'SOUTHEAST');
    v_result := REGEXP_REPLACE(v_result, '\bSW\b', 'SOUTHWEST');
    v_result := REGEXP_REPLACE(v_result, '\bNE\b', 'NORTHEAST');
    v_result := REGEXP_REPLACE(v_result, '\bNW\b', 'NORTHWEST');
    v_result := REGEXP_REPLACE(v_result, '\bE\b', 'EAST');
    v_result := REGEXP_REPLACE(v_result, '\bW\b', 'WEST');
    v_result := REGEXP_REPLACE(v_result, '\bN\b', 'NORTH');
    v_result := REGEXP_REPLACE(v_result, '\bS\b', 'SOUTH');
    v_result := REGEXP_REPLACE(v_result, '\bAVE\b', 'AVENUE');
    v_result := REGEXP_REPLACE(v_result, '\bST\b', 'STREET');
    v_result := REGEXP_REPLACE(v_result, '\bRD\b', 'ROAD');
    v_result := REGEXP_REPLACE(v_result, '\bBLVD\b', 'BOULEVARD');
    
    -- 清洗多余空格：多空格转单空格，去除首尾空格
    v_result := REGEXP_REPLACE(v_result, '\s+', ' ');
    v_result := TRIM(v_result);
    
    RETURN v_result;
END;
/

使用方式 #

SELECT 
    a.address AS addr1,
    b.address AS addr2,
    CASE WHEN normalize_address(a.address) = normalize_address(b.address) THEN 'true' ELSE 'false' END AS is_match
FROM table_a a
JOIN table_b b ON normalize_address(a.address) = normalize_address(b.address);

方案2：查询中直接嵌套正则替换（无需创建函数） #

如果只是临时查询，可直接在SQL中嵌入替换逻辑，无需创建函数：

SELECT 
    a.address AS addr1,
    b.address AS addr2,
    CASE 
        WHEN TRIM(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(a.address), 
            '\b(SE|SW|NE|NW|E|W|N|S|AVE|ST|RD|BLVD)\b', 
            CASE 
                WHEN '\1' = 'SE' THEN 'SOUTHEAST'
                WHEN '\1' = 'SW' THEN 'SOUTHWEST'
                WHEN '\1' = 'NE' THEN 'NORTHEAST'
                WHEN '\1' = 'NW' THEN 'NORTHWEST'
                WHEN '\1' = 'E' THEN 'EAST'
                WHEN '\1' = 'W' THEN 'WEST'
                WHEN '\1' = 'N' THEN 'NORTH'
                WHEN '\1' = 'S' THEN 'SOUTH'
                WHEN '\1' = 'AVE' THEN 'AVENUE'
                WHEN '\1' = 'ST' THEN 'STREET'
                WHEN '\1' = 'RD' THEN 'ROAD'
                WHEN '\1' = 'BLVD' THEN 'BOULEVARD'
            END), '\s+', ' '))
        = TRIM(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(b.address), 
            '\b(SE|SW|NE|NW|E|W|N|S|AVE|ST|RD|BLVD)\b', 
            CASE 
                WHEN '\1' = 'SE' THEN 'SOUTHEAST'
                WHEN '\1' = 'SW' THEN 'SOUTHWEST'
                WHEN '\1' = 'NE' THEN 'NORTHEAST'
                WHEN '\1' = 'NW' THEN 'NORTHWEST'
                WHEN '\1' = 'E' THEN 'EAST'
                WHEN '\1' = 'W' THEN 'WEST'
                WHEN '\1' = 'N' THEN 'NORTH'
                WHEN '\1' = 'S' THEN 'SOUTH'
                WHEN '\1' = 'AVE' THEN 'AVENUE'
                WHEN '\1' = 'ST' THEN 'STREET'
                WHEN '\1' = 'RD' THEN 'ROAD'
                WHEN '\1' = 'BLVD' THEN 'BOULEVARD'
            END), '\s+', ' '))
        THEN 'true' 
        ELSE 'false' 
    END AS is_match
FROM table_a a, table_b b
WHERE TRIM(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(a.address), 
            '\b(SE|SW|NE|NW|E|W|N|S|AVE|ST|RD|BLVD)\b', 
            CASE 
                WHEN '\1' = 'SE' THEN 'SOUTHEAST'
                WHEN '\1' = 'SW' THEN 'SOUTHWEST'
                WHEN '\1' = 'NE' THEN 'NORTHEAST'
                WHEN '\1' = 'NW' THEN 'NORTHWEST'
                WHEN '\1' = 'E' THEN 'EAST'
                WHEN '\1' = 'W' THEN 'WEST'
                WHEN '\1' = 'N' THEN 'NORTH'
                WHEN '\1' = 'S' THEN 'SOUTH'
                WHEN '\1' = 'AVE' THEN 'AVENUE'
                WHEN '\1' = 'ST' THEN 'STREET'
                WHEN '\1' = 'RD' THEN 'ROAD'
                WHEN '\1' = 'BLVD' THEN 'BOULEVARD'
            END), '\s+', ' '))
        = TRIM(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(b.address), 
            '\b(SE|SW|NE|NW|E|W|N|S|AVE|ST|RD|BLVD)\b', 
            CASE 
                WHEN '\1' = 'SE' THEN 'SOUTHEAST'
                WHEN '\1' = 'SW' THEN 'SOUTHWEST'
                WHEN '\1' = 'NE' THEN 'NORTHEAST'
                WHEN '\1' = 'NW' THEN 'NORTHWEST'
                WHEN '\1' = 'E' THEN 'EAST'
                WHEN '\1' = 'W' THEN 'WEST'
                WHEN '\1' = 'N' THEN 'NORTH'
                WHEN '\1' = 'S' THEN 'SOUTH'
                WHEN '\1' = 'AVE' THEN 'AVENUE'
                WHEN '\1' = 'ST' THEN 'STREET'
                WHEN '\1' = 'RD' THEN 'ROAD'
                WHEN '\1' = 'BLVD' THEN 'BOULEVARD'
            END), '\s+', ' '));

注意事项 #

• 正则中的\b是单词边界，可避免替换到单词中间的字符（比如防止把"STONE"中的"ST"替换成"STREET"）
• 替换顺序需优先处理长缩写（如先替换SE/SW，再替换S/E），防止短缩写先匹配导致长缩写被错误拆分
• 可根据业务需求扩展更多缩写/全称对应关系，比如添加LN对应LANE、DR对应DRIVE等
• 若地址存在其他格式差异（如数字与街道名顺序颠倒、额外标点），需补充对应的清洗逻辑

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