如何在BigQuery中查找给定字符串对应的最长匹配字符串

你要实现的是从候选字符串中查找目标字符串的最长前缀匹配，用标准SQL即可实现，不需要额外自定义函数，核心逻辑为：先筛选所有符合「候选字符串是目标字符串前缀」的记录，再按候选字符串长度倒序排序，取第一条即可得到最长匹配结果。

单条目标字符串查询 #

如果只需要匹配1122334455这一个字符串，直接执行以下查询：

SELECT match_str
FROM table_b
WHERE '1122334455' LIKE CONCAT(match_str, '%')
ORDER BY LENGTH(match_str) DESC
LIMIT 1;

执行后返回结果为112233，符合预期。

批量匹配table_a所有记录 #

如果需要对table_a中所有字符串批量计算最长匹配结果，用窗口函数实现更稳妥：

SELECT source_str, longest_match
FROM (
    SELECT 
        a.source_str,
        b.match_str AS longest_match,
        -- 按匹配字符串长度倒序排序，最长的排在第一位
        ROW_NUMBER() OVER (PARTITION BY a.source_str ORDER BY LENGTH(b.match_str) DESC) AS rn
    FROM table_a a
    LEFT JOIN table_b b 
        ON a.source_str LIKE CONCAT(b.match_str, '%') -- 前缀匹配规则
) t
WHERE rn = 1;

常见适配调整 #

• 如果需求不是前缀匹配，是任意位置的子串匹配，将关联条件替换为LOCATE(b.match_str, a.source_str) > 0即可
• 如果存在多个长度相同的最长匹配项需要全部返回，将ROW_NUMBER()替换为RANK()
• 不同数据库的函数适配：
  • SQL Server：用LEN()替换LENGTH()，字符串拼接用+代替CONCAT()
  • Oracle：前缀判断也可以用SUBSTR(a.source_str, 1, LENGTH(b.match_str)) = b.match_str

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