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

Question

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.

Answer 1

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.

Answer 2

declare @str varchar(20)=',1,2,3,'

select   case
         when @str like ',%,' then stuff(stuff(@str, 1, 1, ''),LEN(stuff(@str, 1, 1, '')),1,'')

         when @str like ',%' then stuff(@str, 1, 1, '')

         when @str like '%,' then stuff(@str, LEN(@str), 1, '')

         else @str
         end