SQL子串筛选：提取BookQty并筛选<900的记录（含空值）

问题分析 #

你遇到的报错Invalid length parameter passed to the RIGHT function，是因为当Col1为空或者最后一段（BookQty）为空时，charindex('|', reverse(col1))-1会得到0或负数，而RIGHT函数不允许长度参数为0或负数，导致执行失败。另外直接在WHERE子句中使用未处理的字符串表达式，无法正确保留空值记录。

解决方案 #

推荐使用**CTE（公共表表达式）**先统一提取并处理BookQty字段，再在外部进行筛选，这样既避免重复计算，也能安全处理空值和无效格式的情况：

WITH BookQtyExtracted AS (
    SELECT 
        BookName,
        -- 安全提取最后一段的BookQty，处理空值和格式异常
        CASE
            -- 当Col1为空时直接返回空字符串
            WHEN Col1 IS NULL THEN ''
            -- 当Col1中不足两个分隔符时，返回空
            WHEN CHARINDEX('|', Col1, CHARINDEX('|', Col1) + 1) = 0 THEN ''
            -- 提取最后一个|之后的内容作为BookQty
            ELSE SUBSTRING(Col1, CHARINDEX('|', Col1, CHARINDEX('|', Col1) + 1) + 1, LEN(Col1))
        END AS BookQty
    FROM Table1
)
SELECT 
    BookName,
    BookQty
FROM BookQtyExtracted
WHERE 
    -- 保留空值记录
    BookQty = ''
    -- 筛选数值小于900的有效记录
    OR (TRY_CAST(BookQty AS INT) IS NOT NULL AND TRY_CAST(BookQty AS INT) < 900)

方案说明 #

1. CTE部分：

   • 先判断Col1是否为空，直接返回空字符串；
   • 检查Col1是否包含至少两个|（确保格式符合BookNo|ShelfNo|BookQty），不符合则返回空；
   • 使用SUBSTRING结合两次CHARINDEX定位最后一个|的位置，提取后续内容作为BookQty，比RIGHT+REVERSE的组合更稳定，避免长度参数异常。

2. 筛选条件：

   • 用BookQty = ''保留空值记录；
   • 用TRY_CAST尝试将BookQty转为整数，避免非数值内容导致转换报错，同时筛选出小于900的有效记录。

另一种简化写法（直接在WHERE中处理异常） #

如果不想用CTE，也可以在WHERE子句中通过NULLIF和ISNULL处理长度参数的异常，同时保留空值：

SELECT 
    BookName,
    CASE 
        WHEN CHARINDEX('|', Col1, 1) >= 2 THEN RIGHT(Col1, CHARINDEX('|', REVERSE(ISNULL(Col1, ''))) - 1)
        ELSE ''
    END AS BookQty
FROM Table1
WHERE 
    -- 保留Col1为空或BookQty为空的记录
    Col1 IS NULL
    OR RIGHT(Col1, NULLIF(CHARINDEX('|', REVERSE(ISNULL(Col1, ''))) - 1, 0)) IS NULL
    -- 筛选有效数值且小于900的记录
    OR (TRY_CAST(RIGHT(Col1, CHARINDEX('|', REVERSE(ISNULL(Col1, ''))) - 1) AS INT) < 900)

这里ISNULL(Col1, '')避免REVERSE处理NULL值，NULLIF(..., 0)将长度参数为0的情况转为NULL，让RIGHT返回NULL而不是报错。

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