Click here to Skip to main content
15,849,325 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I want to remove ALL the commas that appear at the beginning of the string and at the end of the string in SQL SERVER. I DON'T WANT TO REMOVE COMMAS WHICH APPEAR WITHIN STRING.
My string is like-
",,test1,test2 tag,test3 tag,,,"

Required o/p=>
"test1,test2 tag,test3 tag"

Please help me. Thank you.

What I have tried:

SELECT REPLACE(LTRIM(RTRIM(REPLACE(',,test1,test2 tag,test3 tag,', ',', ''))), '', ',')

But it gives o\p=>
"test1test2 tagtest3 tag"

which is wrong as it's removing all the commas which are within the string also.
Updated 13-Nov-19 0:51am

Share this answer
There are several ways of doing this. For example you can use a small T-SQL block like this
   DECLARE  @data VARCHAR(100);
   SET @data = ',,test1,test2 tag,test3 tag,,,';
   SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(@data, PATINDEX('%[^,]%', @data),99999)), PATINDEX('%[^,]%', REVERSE(SUBSTRING(@data, PATINDEX('%[^,]%', @data),99999))),99999));

Or embed the logic into a select statement, for example
SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(MyColumn, PATINDEX('%[^,]%', MyColumn),99999)), PATINDEX('%[^,]%', REVERSE(SUBSTRING(MyColumn, PATINDEX('%[^,]%', MyColumn),99999))),99999))
FROM MyTable

However, the easy way would be to create a small function for the task, for example TrimChar to remove desired character[^]
Share this answer
SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(@data, PATINDEX('%[^, ]%', @data),99999)), PATINDEX('%[^, ]%', REVERSE(SUBSTRING(@data, PATINDEX('%[^, ]%', @data),99999))),99999));
Share this answer
CHill60 13-Nov-19 6:06am    
This problem was resolved 3 years ago. You have resurrected a old question by copying someone else's work (Wendelius' Solution 2). Do not do this.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900