执行SQL脚本时遇LEFT/SUBSTRING函数无效长度参数错误求助

这个错误本质是SUBSTRING的长度参数出现了非正数——当你的XMLREQBODY字段里找不到<PersonNumber>标签，或者</PersonNumber>标签的位置在<PersonNumber>开始标签+14位（也就是标签结束的位置）之前时，你用来计算截取长度的表达式charindex('</PersonNumber>',a.XMLREQBODY)-charindex('<PersonNumber>',a.XMLREQBODY)-14就会得到负数或者0，直接传给SUBSTRING就触发了报错。

下面给你两个靠谱的解决方案：

方案1：添加安全判断的字符串截取 #

通过CASE语句先验证标签的位置合法性，只有当长度参数为正数时才执行截取，否则返回NULL或你指定的默认值：

select distinct 
    a.IMPORTBTCHSTATID, 
    b.IMPORTBATCHID, 
    CASE 
        -- 先确认开始标签存在，且结束标签在开始标签的有效位置之后
        WHEN charindex('<PersonNumber>', a.XMLREQBODY) > 0 
             AND charindex('</PersonNumber>', a.XMLREQBODY) > (charindex('<PersonNumber>', a.XMLREQBODY) + 14)
        THEN SUBSTRING(
                a.XMLREQBODY, 
                charindex('<PersonNumber>', a.XMLREQBODY) + 14, 
                charindex('</PersonNumber>', a.XMLREQBODY) - charindex('<PersonNumber>', a.XMLREQBODY) - 14
             )
        ELSE NULL -- 可以替换成''或者其他默认值
    END as PersonNum 
from FAILEDXMLBODY a 
inner join IMPORTBTCHSTAT b on a.IMPORTBTCHSTATID = b.IMPORTBTCHSTATID 
where b.IMPORTBATCHID in ('252...')

方案2：用XML原生解析（更推荐） #

如果你的SQL Server版本是2005及以上，直接把字符串转成XML类型，用XPath提取内容，这比字符串截取可靠得多，还能避免标签大小写、空格、嵌套等问题：

select distinct 
    a.IMPORTBTCHSTATID, 
    b.IMPORTBATCHID, 
    -- 转换XML后提取第一个PersonNumber节点的值，第二个参数是返回值的数据类型
    CAST(a.XMLREQBODY AS XML).value('(//PersonNumber)[1]', 'nvarchar(100)') as PersonNum 
from FAILEDXMLBODY a 
inner join IMPORTBTCHSTAT b on a.IMPORTBTCHSTATID = b.IMPORTBTCHSTATID 
where b.IMPORTBATCHID in ('252...')

注意：如果你的XML带有命名空间，需要先添加WITH XMLNAMESPACES声明来指定命名空间，否则可能提取不到值。

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