SQL CHARINDEX无法正确检索字符串目标索引的解决方法

• 核心问题：CAST([col] AS varchar) 未显式指定长度，SQL Server 中该写法默认将字符串截断为30个字符，若目标子串text位于30位之后，会被直接切掉，导致匹配失败返回0。
• 附加问题：原列是NVARCHAR类型，匹配的字面量未加N前缀，可能引发隐式转换异常。

• 类型转换必须显式指定长度，长度不小于列的实际长度（原列是NVARCHAR(4000)，可直接复用该类型，或转成varchar(4000)）。
• 匹配Unicode字符串时，字面量必须加N前缀，避免隐式转换问题。
• 替代检索方法：若需模糊匹配，可使用PATINDEX函数，支持通配符匹配，返回规则与CHARINDEX一致。

SELECT 
  t.*, 
  -- 直接使用原NVARCHAR列，匹配字面量加N前缀
  CHARINDEX(N'text', t.[col]) AS [out]
FROM (
    SELECT CAST([col] AS NVARCHAR(4000)) AS [col] 
    FROM (
         VALUES ('"http:\/\/xxxxx.share.com\/text"'),
                ('random content in string :\/\\//\ / text \\\''''''')
    ) AS t0([col])
) t

若必须转成varchar类型，需显式指定长度：

SELECT t.*, CHARINDEX('text', CAST([col] AS varchar(4000))) AS [out]
-- 其余逻辑同上

• CHARINDEX语法：CHARINDEX(目标子串, 被检索字符串, [起始查找位置])，找不到时返回0，找到时返回子串首次出现的起始位置（从1开始计数）。
• PATINDEX语法：PATINDEX(模式串, 被检索字符串)，模式串支持%（任意多个字符）、_（单个字符）等通配符，适用于模糊位置匹配的场景，示例：PATINDEX(N'%[^a-z]text[^a-z]%', t.[col]) 可匹配独立的text单词。

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