Nonsense Error in SQL Server Management Studio

Question

I have this query that spawns the following error:

SELECT * FROM Quota
WHERE LEFT(QtLabel, LEN(QtLabel)-2) IN (
'1032',
'3300',
'9682'
)

Msg 536, Level 16, State 5, Line 1 Invalid length parameter passed to the SUBSTRING function.

Am I doing something wrong? It tends to show up when I use the LEN() function. Might it be a datatype issue?

Answer 1

Is it possible that LEN(QtLabel) <= 2 ?

Answer 2

Are you sure that each QtLabel field is longer than 2 characters?

Answer 3

LEFT probably uses SUBSTRING internally. What happens if the length of QtLabel is <= 2?

Answer 4

It's because some QtLabel values don't contain > 2 characters, so you end up trying to do a LEFT() with a negative value as the number to restrict to.

In your scenario, you're assuming all QtLabel values are 6 characters, so uou should do:

SELECT * FROM Quota
WHERE LEN(QtLabel) = 6
AND LEFT(QtLabel, LEN(QtLabel)-2) IN (
'1032',
'3300',
'9682'
)

Answer 5

SELECT LEFT('MyText', -2)

will throw the same error