Hi, this was a nice question, however I don't fully agree with the wording
For Smith, we don't substring it, since S is the first character
SUBSTRING is still used, but since it starts at the first character of the string (start expression < 1), and the length expression is longer than the length of the string, the whole string is returned.
Yup, with one small caveat.
As palotaiarpad says, the question is actually more about SUBSTRING than about CHARINDEX. For the Smith row (and assuming default collations - I am glad someone else pointed that out this time, as I start to grow tired of repeating myself), the CHARINDEX() function returns 1, subtract 1 and the result is 0. The effect of any start position below 1 is that SUBSTRING will return characters from the start of the string, but reduce the effective length by the difference between the start position and 1. So in this case, for Smith the effective SUBSTRING arguments are SUBSTRING ('Smith', 0, 10), which is equivalent to SUBSTRING('Smith', 1, 9). Since Smith has less than 9 characters, all of it is returned.
Try the same code with 'Smithsonain', and you *will* note the difference between a start position of 0 or 1 with a length 10.