TrimEnd equivalent in SQL Server

Question

I have a column (Numbers) that has the following meanings:

1,2,3 1,2,3, 1,2,3,,, 1,2,3,,,,,, 

I want to trim all commas at the end of the line so that the result is

 1,2,3 1,2,3 1,2,3 1,2,3 

I tried below Query, but with this we can only remove one last comma

 DECLARE @String as VARCHAR(50) SET @String='1,2,3,4,,,,,,,,,,,,,,,,' SELECT CASE WHEN right(rtrim(@String),1) = ',' then substring(rtrim(@String),1,len(rtrim(@String))-1) ELSE @String END AS TruncString 

How to remove all commas at the end of a line?

Answer 1

You can do this using:

 LEFT(Numbers, LEN(Numbers) - (PATINDEX('%[^,]%', REVERSE(Numbers)) - 1)) 

The premise of this is that you first change the line using REVERSE :

 REVERSE(Numbers) --> ,,,,,,3,2,1 

Then you can find the position of the first character, which is not a comma, using PATINDEX and pattern matching [^,] :

 PATINDEX('%[^,]%', REVERSE(Numbers)) --> ,,,,,,3,2,1 = 7 

Then you can use the length of the string using LEN to get the opposite position, that is, if the position of the first character that is not a comma is 7 in the inverted string, and the string length is 10, then you need the first 4 characters of the string. Then you use SUBSTRING to extract the corresponding part

Full example:

 SELECT Numbers, Reversed = REVERSE(Numbers), Position = PATINDEX('%[^,]%', REVERSE(Numbers)), TrimEnd = LEFT(Numbers, LEN(Numbers) - (PATINDEX('%[^,]%', REVERSE(Numbers)) - 1)) FROM (VALUES ('1,2,3'), ('1,2,3,'), ('1,2,3,,,'), ('1,2,3,,,,,,'), ('1,2,3,,,5,,,'), (',,1,2,3,,,5,,') ) t (Numbers); 

---

EDIT

In response to the edit, which had some syntax errors, the following are functions for trimming the beginning and trimming both sides of the comma:

 SELECT Numbers, Reversed = REVERSE(Numbers), Position = PATINDEX('%[^,]%', REVERSE(Numbers)), TrimEnd = LEFT(Numbers, LEN(Numbers) - (PATINDEX('%[^,]%', REVERSE(Numbers)) - 1)), TrimStart = SUBSTRING(Numbers, PATINDEX('%[^,]%', Numbers), LEN(Numbers)), TrimBothSide = SUBSTRING(Numbers, PATINDEX('%[^,]%', Numbers), LEN(Numbers) - (PATINDEX('%[^,]%', REVERSE(Numbers)) - 1) - (PATINDEX('%[^,]%', Numbers) - 1) ) FROM (VALUES ('1,2,3'), ('1,2,3,'), ('1,2,3,,,'), ('1,2,3,,,,,,'), ('1,2,3,,,5,,,'), (',,1,2,3,,,5,,') ) t (Numbers); 

Answer 2

Since there are several occurrences, you cannot do this with a simple built-in function expression, but a simple user-defined function can do the job.

 create function dbo.MyTrim(@text varchar(max)) returns varchar(max) as -- function to remove all commas from the right end of the input. begin while (right(@text, 1) = ',' begin set @text = left(@text, len(@text) - 1) end return @text end go 

Answer 3

You can find the first occurrence of ',,' and take everything before this:

 select (case when numbers like '%,,' then left(numbers, charindex(',,', numbers) - 1) when numbers like '%,' then left(numbers, len(numbers) - 1) else numbers end) 

Note. It would seem that you are storing lists of things in a comma delimited string. It is usually best to store them using a connection table.

EDIT:

Or an alternative way to formulate this without case :

 select left(numbers + ',,', charindex(',,', numbers + ',,') - 1) 

Answer 4

Run the query below and get the expected results.

 declare @sql varchar(500) set @sql ='1,2,3,,,,,,' select left(@sql,case charindex(',,',@sql,0) when 0 then len(@sql)-1 else charindex(',,',@sql,0)-1 end) 

Answer 5

 Create FUNCTION TrimStartEndAll ( @string varchar(max), @trimValue varchar(5), @removeall int=0 ) RETURNS varchar(max) AS BEGIN if @removeall=1 while CHARINDEX(@trimValue,@string) >0 and @removeall=1 begin set @string = REPLACE(@string,@trimValue,'') end if @removeall = 0 begin while CHARINDEX(@trimValue,@string) =1 begin set @string = SUBSTRING(@string,len(@trimValue)+1, len(@string)) end while substring(@string,len(@string)-len(@trimValue)+1, len(@trimValue)) = @trimValue begin set @string =substring(@string,0, (len(@string)-len(@trimValue)+1)) end end return @string END GO 

output

 select dbo.TrimStartEndAll( ',,1,2,3,,,5,,,,,,,,,',',,',1) => 1,2,3,5, select dbo.TrimStartEndAll( ',,1,2,3,,,5,,,,,,,,,',',,',0) => 1,2,3,,,5, 

Answer 6

SQL Server 2017 implements an enhanced version of the TRIM function. You can use TRIM (',' FROM '1,2,3 ,,') to get the string '1,2,3'