How do I remove leading and trailing commas from string in SQL server?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 25-Sep-16 23:03pm

Er. Shailesh

Updated 12-Nov-19 23:51pm

Wendelius

v4

Add a Solution

3 solutions

Solution 1

check this SQL Server TRIM character[^]

Posted 25-Sep-16 23:42pm

Karthik_Mahalingam

Solution 3

SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(@data, PATINDEX('%[^, ]%', @data),99999)), PATINDEX('%[^, ]%', REVERSE(SUBSTRING(@data, PATINDEX('%[^, ]%', @data),99999))),99999));

Posted 12-Nov-19 23:52pm

raksha.dokania

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2016-09-26T05:45:00

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[^]