Suppose I have a string
One way to deal with "trimming" commas like that would be using a
CASE WHEN str LIKE ',%,' THEN SUBSTRING(str, 2, LEN(str)-2) WHEN str LIKE ',%' THEN RIGHT(str, LEN(str)-1) WHEN str LIKE '%,' THEN LEFT(str, LEN(str)-1) ELSE str END
This is very much self-explanatory: the
CASE statement considers three situations -
strhas commas on both sides,
strstarts in a comma, but does not end in one, and
strends in a comma, but does not start in one.
In the first case, the first and the last characters are removed; in the second case, the leftmost character is removed; in the last case, the trailing character is removed.