Invalid length parameter passed to the substring function

Cause

This is because of negative length parameter pass to substring function.

select SUBSTRING('abc' ,1,charindex('@','abc')-1)

In above query, @ char is not find in string "abc", hence charindex return "-1" as value for length parameter.
So it cause error:
Server: Msg 536, Level 16, State 3, Line 4
Invalid length parameter passed to the substring function.

Solution:

We can use absolute "abs" function for resolution of above problem. In below examples, we are using "abs" over the charindex function so that it will always return positive length parameter to substring/left/right.

select SUBSTRING('abc @ 3' ,1,charindex('@','abc @ 3')-2)

select SUBSTRING('abc @ 3' ,1,abs(charindex('@','abc @ 3')-2))



OR

Workaround can be using reverse function:
1. first reverse the string using "reverse" function
2. then substring the string using all positive length parameter
e.x.
select  reverse(
substring(reverse('abc @ 3'), charindex('@',reverse('abc @ 3'))+2,len('abc @ 3'))
)

3. again reverse the string, to bring string in actual shape.