Arbaaz Arbaaz - 1 year ago 63
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

I would like to remove the last
or if the string looks like
I would still like to get
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 Source

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

    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

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download