Space at the end of string
When checking if a string is empty or not, append a non-space character to it and compare the result to the same character. This

@string + '.' &lt;&gt; '.'


will evaluate to TRUE only when the @string is truly empty. The @string's trailing spaces will count (because they will no longer be trailing after appending the .).

And you can go similarly about finding the length. This

LEN(@string + '.')


will only ever evaluate to 1 if the @string is '', not when it's '&nbsp;' or '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' or anything of the kind.

By the way, when you want the SUBSTRING function to return a substring from a certain position to the end of the string, you can specify any fairly large number as the length parameter, no need to use LEN(). Very often something like 

SUBSTRING(@string, 2, 999999999)


would do.