How to replace first and last character of column in sql server?

Question

I have a database column and its give a string like ,Recovery, Pump Exchange,.

I want remove first and last comma from string.

Expected Result : Recovery, Pump Exchange.

Answer 1

You can use SUBSTRING for that:

SELECT
    SUBSTRING(col, 2, LEN(col)-2)
FROM ...

Obviously, an even better approach would be not to put leading and trailing commas there in the first place, if this is an option.

I want to remove last and first comma only if exist otherwise not.

The expression becomes a little more complex, but the idea remains the same:

SELECT SUBSTRING(
    col
,  CASE LEFT(@col,1) WHEN ',' THEN 2 ELSE 1 END
,  LEN(@col) -- Start with the full length
             -- Subtract 1 for comma on the left
      - CASE LEFT(@col,1) WHEN ',' THEN 1 ELSE 0 END 
             -- Subtract 1 for comma on the right
      - CASE RIGHT(@col,1) WHEN ',' THEN 1 ELSE 0 END
)
FROM ...