在MS SQL中，对使用RIGHT和CHARDINDEX的计算列的索引会导致传递给RIGHT的无效长度参数

Question

我有这个计算列

ALTER TABLE mytable
ADD vMessage AS (CONVERT([nvarchar]  (200),RIGHT(Message,CHARINDEX('.',REVERSE(Message),1)-1),0))

我正在尝试在vMessage上建立索引

CREATE NONCLUSTERED INDEX [IX_vMessage]
ON mytable ([vMessageType])

我收到这个错误

Invalid length parameter passed to the RIGHT function.

创建计算列并运行此查询工作于

SELECT 
Message,
RIGHT(Message,charindex('.',reverse(Message),1)-1),
CONVERT([nvarchar](200),RIGHT(Message,CHARINDEX('.',REVERSE(Message),1)-1),0)
FROM mytable

数据与此示例数据相似。

DECLARE @mytable TABLE (message nvarchar(1024))
INSERT INTO @mytable (message) VALUES 
('Services.Common.Contracts.InternalContract'),
('Services.Common.Contracts.ItemArchivedContract'),
('Services.Common.Contracts.ItemCreatedContract'),
('Services.Common.Contracts.ItemInformationUpdatedContract'),
('Services.Common.Contracts.EmailContract'),
('Services.Common.Contracts.Customer.SetCredentialsContract'),
('Services.Common.Contracts.InternalItemContract')
SELECT 
Message,
RIGHT(Message,charindex('.',reverse(Message),1)-1),
CONVERT([nvarchar](200),RIGHT(Message,CHARINDEX('.',REVERSE(Message),1)-1),0)
FROM @myTable

没有空消息。所有邮件中至少有一个句点。以下内容不返回任何内容。

SELECT * FROM mytable WHERE CHARINDEX('.', Message) = 0

Answer 1

问题是，如果CHARINDEX找不到要搜索的值，它可以返回0。为了避免因此导致错误，您应该始终将CHARINDEX放入NULLIF以使0为空

ALTER TABLE mytable
ADD vMessage AS (
CONVERT(nvarchar(200),
RIGHT(
Message,
NULLIF(
CHARINDEX(
'.',
REVERSE(Message)
),
0
) - 1
)
)
);

我说总是，因为使用WHERE并不总是有帮助，因为SQL Server经常重新排列表达式。NULLIF在内部使用CASE，这是避免这种情况发生的唯一保证方法。

Answer 2

代码首先反转字符串，然后使用CHARINDEX()查找.字符。由于不希望在最终结果中保留.，因此从返回值中减去1。这就是问题的症结所在。

如果找不到值，CHARINDEX()将返回0(对字符串使用基于1而非基于0的索引(。当我们从中减去1并将其传递给RIGHT()函数时，您有一个无效的参数，并将看到此错误。

但我也看到了这一点：

所有消息中至少有一个点字符。

我建议再次检查。也许测试是在与生产环境不同的环境中运行的。当我们将您的查询加载到db fidd:时，我们可以看到您的查询在提供的样本数据上运行良好

https://dbfiddle.uk/bUMPVALz