[SOLVED] How to remove a specific character from a string, only when it is the first or last character in the string.

How to remove a specific character from a string, only when it is the first or last character in the string.

Suppose I have a string 1,2,3, I would like to remove the last , or if the string looks like ,1,2,3, or ,1,2,3 I would still like to get 1,2,3 as my result. And please try to be a little explanatory in your answer. I do not just want to copy paste stuff without understanding it. Thank you.

Solution

One way to deal with "trimming" commas like that would be using a CASE statement:

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 -

When the string str has commas on both sides,
When the string str starts in a comma, but does not end in one, and
When the string str ends 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.

Demo on sqlfiddle.