Postgres中如何判断子串是否存在于数组的任意元素值中？

实现思路 #

• 先将address_list按分号拆分，得到每条独立的「街道+门牌号列表」条目
• 对所有条目统一做小写、移除空格处理，消除大小写、空格带来的格式匹配差异
• 逐个匹配条目：先判断街道是否一致，再判断门牌号是否独立存在，避免出现门牌号14误匹配4、跨街道匹配的问题

完整实现代码 #

SELECT EXISTS (
    SELECT 1
    FROM unnest(string_to_array(lower(REPLACE(address_list, ' ', '')), ';')) AS single_addr
    WHERE 
        -- 匹配对应街道
        single_addr LIKE lower(REPLACE(street_a, ' ', '')) || '%'
        -- 匹配独立存在的门牌号，覆盖所有门牌号位置场景
        AND (
            single_addr LIKE '%,' || number_a || ',%' -- 门牌号在中间
            OR single_addr LIKE '%' || lower(REPLACE(street_a, ' ', '')) || number_a || ',%' -- 门牌号是第一个
            OR single_addr LIKE '%,' || number_a -- 门牌号是最后一个
            OR single_addr LIKE '%' || lower(REPLACE(street_a, ' ', '')) || number_a -- 门牌号是唯一值
        )
) AS match_result

简化版（PostgreSQL环境适用） #

可以用PostgreSQL正则元字符\m（单词开头）和\M（单词结尾）简化写法，自动匹配独立门牌号：

SELECT EXISTS (
    SELECT 1
    FROM unnest(string_to_array(lower(REPLACE(address_list, ' ', '')), ';')) AS single_addr
    WHERE single_addr ~ ('^' || lower(REPLACE(street_a, ' ', '')) || '.*\m' || number_a || '\M')
) AS match_result

效果验证 #

• 对于示例1：street_a='Main Street'、number_a='4'、address_list='Lower Street 6;Main Street 3,4,5'，返回TRUE
• 对于示例2：street_a='Main Street'、number_a='4'、address_list='Main Street 1,2;Lower Street 4'，返回FALSE，不会出现跨街道误匹配问题

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