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:

SQL
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.
Posted
Updated 13-Nov-19 0:51am
v4

 
Share this answer
 
There are several ways of doing this. For example you can use a small T-SQL block like this
SQL
BEGIN 
   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));
END;

Or embed the logic into a select statement, for example
SQL
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
 
Comments
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