LIKE '%sub%' 常被误用是因为它强制全表扫描、受排序规则影响且语义非“包含”而是模式匹配；但因跨数据库兼容性无可替代。SQL Server 中更稳的写法是 CHARINDEX('sub', str) > 0，语义明确、执行计划稳定、不依赖通配符解析。
LIKE '%sub%' 为什么常被误用但又难以替代
LIKE 是唯一跨所有 SQL 数据库都支持的子串判断方式，但它本质是模式匹配，不是“包含”语义。写 WHERE column LIKE '%error%' 看似直白，实际会强制全表扫描（除非是前缀匹配如 'error%'），且受 collation 影响：比如在大小写敏感排序规则下，'ERROR' 不会被 LIKE '%error%' 匹配到。
常见错误现象：WHERE name LIKE 'a%' 被当成“包含 a”，其实只是“以 a 开头”；而 WHERE name LIKE '%a%' 在字段很长、数据量大时性能陡降，尤其当该字段没建全文索引时。
- 适用场景：需要模糊逻辑（如“含数字+字母组合”）、或必须兼容多数据库（MySQL/PostgreSQL/SQL Server）
- 注意点：避免在 WHERE 中拼接变量构造
LIKE '%' + @var + '%'，易引发参数嗅探或执行计划缓存问题 - 大小写控制：显式转大小写更可靠，例如
UPPER(column) LIKE UPPER('%error%')
CHARINDEX('sub', str) > 0 是 SQL Server 最稳的写法
CHARINDEX 是 SQL Server 原生函数，返回子串首次出现位置（从 1 开始），找不到返回 0。它不依赖排序规则，二进制比较，对大小写敏感与否完全由字段本身的 collation 决定，行为可预测。
常见错误现象：把参数顺序写反——CHARINDEX(str, 'sub') 会报错，正确是 CHARINDEX('sub', str)；另外，任一参数为 NULL，结果直接是 NULL，导致整行被 WHERE 过滤掉（三值逻辑），不是预期的“不匹配”。
- 推荐写法：
WHERE CHARINDEX('http://', url) > 0，语义清晰，执行计划稳定 - 和 LIKE 性能对比：两者在无索引时基本一致，但
CHARINDEX更容易被查询优化器识别为简单表达式，利于后续逻辑推导 - 空值防御：加
AND str IS NOT NULL或用ISNULL(str, '')包裹，避免意外丢行
为什么别用 PATINDEX 或 CONTAINS 替代 CHARINDEX
PATINDEX 支持通配符（如 PATINDEX('%[0-9]%', str)），但代价是解析开销更大，且同样无法走索引；CONTAINS 属于全文检索范畴，必须提前建全文索引、配置目录，且只支持英文词干、不支持纯子串（如 CONTAINS(col, 'abc') 不等于“包含 abc”，而是“包含以 abc 为词根的单词”）。
常见错误现象：在没建全文索引的字段上用 CONTAINS，直接报错 Msg 7601, Level 16；用 PATINDEX('%error%', str) 替代 CHARINDEX，性能反而下降 15–20%，且语义超纲——你只是想查“有没有”，不是“是否符合某模式”。
- 真实需求匹配：只要判断存在性，就用
CHARINDEX；需正则级能力（如“含连续3个数字”）才考虑PATINDEX - 全文场景例外：日志分析类大文本、需支持近义词/权重排序时，才值得投入
CONTAINS生态
CHARINDEX 和 LIKE 在执行计划里到底差在哪
看执行计划时，CHARINDEX('x', col) > 0 通常显示为 Compute Scalar + Seek/Scan，而 col LIKE '%x%' 几乎总是显示为 Index Scan 或 Table Scan，哪怕字段上有索引。这是因为后者无法将通配符转换为索引查找区间，优化器只能放弃 SEEK。
但一个关键细节常被忽略：如果字段类型是 varchar(max) 或 nvarchar(max)，CHARINDEX 在内部仍可能触发 LOB 读取开销；而 LIKE 对 max 类型字段有时反而有短路优化（尤其匹配失败时）。所以不能单看“函数 vs 操作符”，得结合字段长度分布测试。
- 实测建议：对平均长度 CHARINDEX；对日志类长文本，先用
LEN(col) 过滤再CHARINDEX，比直接LIKE更可控 - 隐式转换陷阱：
CHARINDEX(N'中文', col)中若col是varchar，会触发列级隐式转换，丢失索引；统一用nvarchar参数或显式CONVERT(nvarchar, col)
