SQL Server中SUBSTRING在FROM子句正常但WHERE子句报错问题

错误原因 #

SQL Server的查询优化器会基于执行成本调整执行顺序，不会严格按照你编写SQL的语句顺序执行：

• 你原本预期先提取所有符合格式的用户名，再过滤掉FOO，但优化器可能优先对所有行执行WHERE子句中的SUBSTRING判断，包括那些Message字段不符合Unauthorized attempt from account [用户名] to access [请求URL]格式的行。
• 这类不符合格式的行，调用CHARINDEX获取的[或]位置无效，会导致SUBSTRING的长度参数为负数或0，触发Invalid length parameter passed to the LEFT or SUBSTRING function错误。
• 即使改用子查询，优化器也可能重写查询逻辑，将子查询中的提取操作提前，依然会处理无效格式的行，导致报错。

解决方案 #

1. 先过滤有效格式的行 #

先通过CHARINDEX校验Message中[和]的位置有效性，再执行用户名提取和过滤：

SELECT SUBSTRING(Message, CHARINDEX('[', Message) + 1, CHARINDEX(']', Message) - CHARINDEX('[', Message) - 1) AS Username
FROM SystemLog
WHERE CHARINDEX('[', Message) > 0 
  AND CHARINDEX(']', Message) > CHARINDEX('[', Message) + 1
  AND SUBSTRING(Message, CHARINDEX('[', Message) + 1, CHARINDEX(']', Message) - CHARINDEX('[', Message) - 1) <> 'FOO'

2. 使用TRY_SUBSTRING容错（SQL Server 2016+） #

TRY_SUBSTRING在参数无效时会返回NULL，再过滤掉NULL和目标用户名：

SELECT TRY_SUBSTRING(Message, CHARINDEX('[', Message) + 1, CHARINDEX(']', Message) - CHARINDEX('[', Message) - 1) AS Username
FROM SystemLog
WHERE Username IS NOT NULL 
  AND Username <> 'FOO'

3. 用CTE先筛选有效行 #

通过公用表表达式（CTE）先提取并筛选格式正确的记录，再进行后续过滤：

WITH ValidLogs AS (
    SELECT 
        Message,
        SUBSTRING(Message, CHARINDEX('[', Message) + 1, CHARINDEX(']', Message) - CHARINDEX('[', Message) - 1) AS Username
    FROM SystemLog
    WHERE CHARINDEX('[', Message) > 0 
      AND CHARINDEX(']', Message) > CHARINDEX('[', Message) + 1
)
SELECT Username
FROM ValidLogs
WHERE Username <> 'FOO'

内容的提问来源于stack exchange，提问作者Green Grasso Holm