In MS SQL an index on a computed column that uses RIGHT and CHARINDEX results in Invalid length para-CodePudding

I have this computed column

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

And I am trying to make an index on vMessage

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

I get this error

Invalid length parameter passed to the RIGHT function.

Creating the computed column and running this query works

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

The data is similar to this sample data.

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

No message is empty. All messages have at least one dot character in them. Below returns nothing.

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

CodePudding user response：

The problem is that CHARINDEX can return 0 if it doesn't find the value being searched for. To avoid causing errors because of this, you should always put CHARINDEX into NULLIF to null out the 0

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

I say always, because using a WHERE doesn't always help, as SQL Server often rearranges expressions. NULLIF uses a CASE internally, which is the only guaranteed way for this not to happen.

CodePudding user response：

The code starts by reversing the string and then using CHARINDEX() to look for a . character. Since you don't want to keep the . in the final result, you then subtract 1 from the returned value. This is where the problem comes in.

CHARINDEX() returns 0 if the value isn't found (using 1-based rather than 0-based indexing for the string). When we subtract 1 from that and pass it to the RIGHT() function, you have an invalid argument and will see this error.

But I also see this:

All messages have at least one dot character in them.

I suggest checking that again. Perhaps the test is running in a different environment from production. We can see your query runs fine on the provided sample data when we load it to db fiddle:

https://dbfiddle.uk/bUMPVALz