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:
SELECT LEFT(ConcatValues, LEN(ConcatValues)-1) AS 'TheValues'<o:p></o:p>
FROM SomeTable t<o:p></o:p>
CROSS APPLY ( SELECT t2.SomeField + ’, ‘ AS [text()]<o:p></o:p>
FROM SomeTable t2<o:p></o:p>
WHERE t.SomeField = t2.SomeField<o:p></o:p>
FOR XML PATH('‘) ) C (ConcatValues)<o:p></o:p>
Msg 537, Level 16, State 5, Line 1<o:p></o:p>
Invalid length parameter passed to the LEFT or SUBSTRING function.<o:p></o:p>
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, '‘)<o:p></o:p>
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"