jason jason - 22 days ago 7
SQL Question

Invalid length parameter passed to the LEFT or SUBSTRING function (SQL Server)

I have this query :

CASE
WHEN ISNUMERIC(SUBSTRING(dbo.Post.OdaSayisi, 1, CHARINDEX('+', dbo.Post.OdaSayisi) - 1)) = 1
THEN SUBSTRING(dbo.Post.OdaSayisi, 1, CHARINDEX('+', dbo.Post.OdaSayisi) - 1)
ELSE 0
END AS RoomNumber,


where
OdaSayisi
is a string like 3+1, 4+2. When I try to write this query :

SELECT *
FROM [myDB].[dbo].[MyView]
WHERE RoomNumber = 3


I get this error in the title. How can I fix this? Thanks.

Answer

The problem is that some values of OdaSayisi do not have a '+' in them. The simplest solution is to put one in for the CHARINDEX() functions

(CASE WHEN ISNUMERIC(SUBSTRING(dbo.Post.OdaSayisi, 1, CHARINDEX('+', dbo.Post.OdaSayisi + '+') - 1)) = 1
      THEN SUBSTRING(dbo.Post.OdaSayisi, 1, CHARINDEX('+', dbo.Post.OdaSayisi + '+') - 1)
      ELSE 0
 END) AS RoomNumber,

The + '+' ensures that CHARINDEX() will not return a negative number.