SQL Server RIGHT函数返回一个不包含null或空值的错误

Question

下面的SQL Server函数可以很好地处理简单的示例，但当它被传递到一个有数百万条记录的列时，它会给出如下错误：

问题：问题的原因是什么，可以解决？

功能：

Create FUNCTION MyFunction(@var varchar(300))
RETURNS varchar(300)
AS
BEGIN
IF LEN(ISNULL(@var, '')) = 0
return @var
declare @varLen tinyint = LEN(@var)
SET @var = CASE
WHEN LEFT(@var,4) = 'abc ' THEN RIGHT(@var,@varLen-4)
WHEN LEFT(@var,4) = 'abc.' THEN RIGHT(@var,@varLen-4)
WHEN LEFT(@var,3) = 'rs ' THEN RIGHT(@var,@varLen-3)
WHEN LEFT(@var,4) = 'rs. ' THEN RIGHT(@var,@varLen-4)
ELSE @var
END
RETURN @var
END

简单查询[正确返回预期输出]：

declare @t varchar(150) = 'abc ewotiu ryire'
select dbo.TestFunction(@t)  --returns "ewotiu ryire"

给出错误的示例查询：

select myColumn MyFunction(myColumn) from myTable

备注：

1. myColumn有数百万条记录，没有空值，大约有10个空值(空白''(。但我已经在上面代码的第一行中处理了这种情况。我已经测试过，当我传递null或空白字符串时，它会正确地返回null或空字符串
2. 我也尝试过使用DATALENGTH函数而不是LEN，但仍然存在完全相同的错误
3. 使用Azure SQL数据库：Microsoft SQL Azure (RTM) - 12.0.2000.8 Sep 18 2021 19:01:34 Copyright (C) 2019 Microsoft Corporation
4. 在线搜索(比如这个或这个等(并没有帮助——可能我的问题有点不同，因为它涉及数百万条记录或其他什么

错误：[传递给具有百万条记录的查询时]

消息536，级别16，状态2传递给RIGHT函数的长度参数无效。

Answer 1

如果将'abc '或'rs '传递给函数，您将看到此错误，因为LEN不计算尾随空格。

因此，对于'rs '，表达式RIGHT(@var,@varLen-3)最终为RIGHT('rs ',2-3)，而-1是无效长度。

您可以使用以下内容直接指定所需的起始位置，而不是使用LEN进行计算

CREATE OR ALTER FUNCTION MyFunction(@var VARCHAR(300))
RETURNS VARCHAR(300)
AS
BEGIN
RETURN CASE WHEN @var LIKE 'abc %' OR @var LIKE 'abc.%' OR @var LIKE 'rs. %' THEN SUBSTRING(@var, 5, 300)
WHEN @var LIKE 'rs %' THEN SUBSTRING(@var, 4, 300)
ELSE @var
END
END 

切换到LIKE的原因是，尾部空格在字符串比较部分中也变得重要。否则，'rs'和'rs '也会比较相等(这也会在原始函数中产生错误，需要加以防范才能在重写中得到所需的结果(。

Answer 2

来说明您的问题：

declare @a varchar(300)
set @a = 'abc'
if LEFT(@a,4) = 'abc '
select LEN(@a)-4
-----------
-1
(1 row(s) affected)

为了说明您的代码中的另一个问题

declare @a varchar(300)
set @a = REPLICATE('A',300)
declare @varLen tinyint = LEN(@a)
Msg 220, Level 16, State 2, Line 4
Arithmetic overflow error for data type tinyint, value = 300.