Arbaaz Arbaaz - 4 months ago 8
SQL Question

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.

Answer

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.

Comments