SQL Server长文本模糊匹配：PATINDEX函数通配符查询指南

PATINDEX 为什么比 LIKE 更适合模糊定位

因为 PATINDEX 返回的是匹配位置（从 1 开始），而 LIKE 只能做布尔判断。当你需要知道“某个模式出现在第几个字符”，或者要结合 SUBSTRING 截取后续内容时，PATINDEX 是唯一选择。

常见误用是把它当 LIKE 的替代品写在 WHERE 里只判是否大于0 —— 这样性能没优势，还失去了定位能力。

• PATINDEX('%box%', product_name) 返回 5，说明 “box” 从第 5 位开始
• PATINDEX('Hello%', 'Hello World') 返回 1，因为匹配从开头起
• PATINDEX('%end', 'no end') 返回 4，% 放尾部表示“以 end 结尾”
• 若没匹配，返回 0；若 pattern 为 NULL，返回 NULL；若 expression 为 NULL，直接报错

通配符写法必须带前后 %，否则行为反直觉

很多人写 PATINDEX('abc', col) 想找包含 abc 的字段，结果全返回 0 —— 因为 PATINDEX 不自动补 %，必须显式写出。

规则很简单：% 表示零个或多个任意字符，_ 表示一个任意字符。但注意：% 必须出现在 pattern 两端（或至少一端），除非你明确查开头或结尾。

• 查中间含 “202[0-9]”：用 PATINDEX('%202[0-9]%', text)
• 查以数字开头：用 PATINDEX('[0-9]%', text)（不用 % 在前）
• 查以 “.pdf” 结尾：用 PATINDEX('%.pdf', text)（不用 % 在后）
• 错例：PATINDEX('202[0-9]', text) —— 这只会匹配整个字符串恰好等于该模式，几乎总返回 0

处理长文本时要注意返回类型

当 expression 是 varchar(max) 或 nvarchar(max) 时，PATINDEX 返回 bigint，否则返回 int。如果忽略这点，在大文本中匹配靠后的位置（比如第 300 万字符），用 int 变量接会溢出变成负数或截断。

实际写法建议统一用 CAST(PATINDEX(...) AS bigint)，尤其在和 SUBSTRING 配合时：

SELECT 
   SUBSTRING(long_text, 
     CAST(PATINDEX('%start%', long_text) AS bigint) + 6, 
     100) AS extracted
FROM docs

另外，PATINDEX 对排序规则敏感。如果字段是 Latin1_General_BIN，而你按常规大小写不敏感方式写 pattern，可能匹配失败。必要时加 COLLATE SQL_Latin1_General_CP1_CI_AS 显式指定。

不能在 LINQ to Entities 中直接调用 SqlFunctions.PatIndex

如果你用 EF6 写 C# 查询，看到 SqlFunctions.PatIndex 就想直接用，会遇到运行时报错：Method 'PatIndex' is not supported —— 因为它只能出现在查询表达式树里，不能当普通方法调用。

正确姿势是把它嵌在 Where 或 Select 的表达式中：

ctx.Products.Where(p => 
   SqlFunctions.PatIndex("%box%", p.Name) > 0)

EF Core 不支持 SqlFunctions，得用 EF.Functions.Like 或原始 SQL。而 PATINDEX 的定位能力在 EF Core 中没有等价封装，真要用就得写 FromSqlRaw。

容易被忽略的一点：SQL Server 2025（17.x）已支持原生正则函数如 REGEXP_LIKE，但目前（2026 年 4 月）仍处于预览阶段，生产环境慎用；PATINDEX 虽老，却是最稳的通配符定位方案。