SQL Server嵌套查询SUBSTRING报错 如何按paramName筛选数据

问题根因 #

SQL Server的查询优化器可能会将外层的paramName过滤条件下推到子查询内部执行，打乱了「先过滤符合%#%$%格式的行、再执行SUBSTRING截取」的执行顺序，导致部分没有#或者$的行提前触发SUBSTRING的长度参数为负的报错。

可行修改方案 #

方案1：用CASE判断规避非法截取（兼容性最高） #

在SUBSTRING执行前先判断格式是否符合，从根源避免长度错误，不受SQL Server版本和优化策略影响：

SELECT * FROM (
  SELECT 
    CASE WHEN CHARINDEX('#', [value]) > 0 AND CHARINDEX('$', [value]) > CHARINDEX('#', [value])
         THEN SUBSTRING([value], 1, CHARINDEX('#', [value]) - 1) 
         ELSE NULL END AS paramNamespace,
    CASE WHEN CHARINDEX('#', [value]) > 0 AND CHARINDEX('$', [value]) > CHARINDEX('#', [value])
         THEN SUBSTRING([value], CHARINDEX('#', [value]) + 1, CHARINDEX('$', [value]) - CHARINDEX('#', [value]) - 1) 
         ELSE NULL END AS paramName,
    CASE WHEN CHARINDEX('#', [value]) > 0 AND CHARINDEX('$', [value]) > CHARINDEX('#', [value])
         THEN SUBSTRING([value], CHARINDEX('$', [value]) + 1, LEN([value])) 
         ELSE NULL END AS paramValue
  FROM STRING_SPLIT(REPLACE((SELECT data FROM test WHERE Id = 1), '{CRLF}', CHAR(7)), CHAR(7))
  WHERE [value] LIKE '%#%$%'
) a
WHERE paramName IN ('name', 'name3')

方案2：强制子查询先执行过滤 #

通过OFFSET 0 ROWS语法强制子查询的过滤、截取逻辑先执行完成，再处理外层的过滤条件，阻止优化器的条件下推行为，写法更简洁：

SELECT * FROM (
  SELECT 
    SUBSTRING([value], 1, CHARINDEX('#',[value]) - 1) as paramNamespace,
    SUBSTRING([value], CHARINDEX('#',[value]) + 1, CHARINDEX('$',[value]) - CHARINDEX('#',[value]) - 1) as paramName,
    SUBSTRING([value], CHARINDEX('$',[value]) + 1, LEN([value])) as paramValue
  FROM STRING_SPLIT(REPLACE((SELECT data FROM test WHERE Id = 1), '{CRLF}', CHAR(7)), CHAR(7))
  WHERE [value] LIKE '%#%$%'
  OFFSET 0 ROWS -- 强制子查询计算优先执行
) a
WHERE paramName IN ('name', 'name3')

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