On a semi-regular basis I'd need to squish a list of comma-delimited values all concatenated together into one field, so I'd use this old snippet:
Problem is when the concatenated field has length zero, LEFT gets a -1 length and returns an error:
SELECT LEFT(ConcatValues, LEN(ConcatValues)-1) AS 'TheValues'
FROM SomeTable t
CROSS APPLY ( SELECT t2.SomeField + ', ' AS [text()]
FROM SomeTable t2
WHERE t.SomeField = t2.SomeField
FOR XML PATH('') ) C (ConcatValues)
Msg 537, Level 16, State 5, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
So today I had a look around and found a better way from this StackOverflow thread. Just replace the LEFT function with this:
STUFF(ConcatValues, LEN(ConcatValues), 1, '')
It gives the exact same results except that the STUFF function is way more tolerant of being given inappropriate length parameters and just returns NULL (which is exactly what I want) instead of throwing errors. From the MSDN documentation:
"If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string"