SQL SUBSTRING函数报错：无效长度参数，如何处理特殊字符串解析？

算力阿峰

2026-6-18

解决SQL Server中SUBSTRING因位置参数负数导致的报错问题

问题描述

现有如下字符串需要解析，提取known as之前的有效名称部分：

Person is (Christopher Bowles known as Chris)
This is a test entry
Identified as Jonathan Sykes known as John)
Registered as (Patrick Joseph known as Pat) (Or also Patty)
Guy called as Richard Smith known as Rich) (Or also Richie)

原查询语句在处理部分字符串时报错：

SELECT DisplayName = LTRIM(RTRIM(SUBSTRING(n.name, CHARINDEX('(', n.name) + 1, (CHARINDEX('known as', n.name) - CHARINDEX('(', n.name)) - 1)))
FROM #nameTest n
WHERE 1 = 1
AND n.name LIKE '%known as%'

报错信息：

Invalid length parameter passed to the LEFT or SUBSTRING function.

报错原因

原语句默认假设左括号(一定出现在known as之前，但部分字符串要么没有左括号，要么左括号在known as之后，导致CHARINDEX('(', n.name)返回0或大于CHARINDEX('known as', n.name)，计算出的SUBSTRING长度参数为负数，触发SQL Server的参数校验错误。

解决方案

通过CASE表达式判断左括号与known as的位置关系，分两种情况处理：

若左括号存在且在known as之前：提取左括号后到known as前的内容
否则：提取字符串开头到known as前的内容

修正后的查询语句：

SELECT 
    DisplayName = LTRIM(RTRIM(
        CASE 
            WHEN CHARINDEX('(', n.name) > 0 AND CHARINDEX('(', n.name) < CHARINDEX('known as', n.name)
            THEN SUBSTRING(n.name, CHARINDEX('(', n.name) + 1, CHARINDEX('known as', n.name) - CHARINDEX('(', n.name) - 1)
            ELSE SUBSTRING(n.name, 1, CHARINDEX('known as', n.name) - 1)
        END
    ))
FROM #nameTest n
WHERE n.name LIKE '%known as%'