如何实现两表基于pattern后仅跟非字母规则的Inner Join

数据表结构与数据 #

name_table表 #

pattern_table表 #

需求说明 #

需要对两张表执行Inner Join操作，匹配规则为：

• name字段与pattern字段完全相等
• 或name以pattern开头，且pattern之后的所有字符均为非字母字符

现有SQL问题 #

当前使用的SQL语句如下：

select 
    A.name, B.pattern 
from 
    name_table A 
inner join 
    pattern_table B on A.name like B.pattern +'[^a-zA-Z]%'

该语句存在匹配错误：

• 当pattern为ssn时，会错误匹配ssn_12nam（后续包含字母nam）
• 当pattern为ss时，会错误匹配ssn、ssn123（后续包含字母n）

期望输出结果 #

解决方案 #

以下是修正后的SQL语句，能够准确满足匹配规则：

SELECT 
    A.name, B.pattern
FROM 
    name_table A
INNER JOIN 
    pattern_table B ON 
        -- 匹配完全相等的情况
        A.name = B.pattern
        OR (
            -- 确保name以pattern开头
            A.name LIKE B.pattern + '%'
            -- 确保pattern之后的所有字符不含字母
            AND SUBSTRING(A.name, LEN(B.pattern) + 1, LEN(A.name)) NOT LIKE '%[a-zA-Z]%'
        )

逻辑说明 #

1. 第一个条件A.name = B.pattern直接覆盖完全匹配的场景，比如ssn匹配ssn。
2. 第二个条件分为两部分：
   • A.name LIKE B.pattern + '%'保证name以指定的pattern开头；
   • SUBSTRING(A.name, LEN(B.pattern) + 1, LEN(A.name)) NOT LIKE '%[a-zA-Z]%'截取pattern之后的所有字符，确保其中不包含任何字母，彻底排除后续带字母的错误匹配。

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