TrimEnd Equivalent in SQL Server
You can search for the first occurrence of ',,'
and take everything before that:
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 better to store these using a junction table.
EDIT:
Or, an alternative way of formulating this without the case
:
select left(numbers + ',,', charindex(',,', numbers + ',,') - 1)
Because there are multiple occurrences you can't do it with a simple builtin 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
You can do this using:
LEFT(Numbers, LEN(Numbers) - (PATINDEX('%[^,]%', REVERSE(Numbers)) - 1))
The premise of this is you first reverse the string using REVERSE
:
REVERSE(Numbers) --> ,,,,,,3,2,1
You then find the position of the first character that is not a comma using PATINDEX
and the pattern match [^,]
:
PATINDEX('%[^,]%', REVERSE(Numbers)) --> ,,,,,,3,2,1 = 7
Then you can use the length of the string using LEN
, to get the inverse position, i.e. if the position of the first character that is not a comma is 7 in the reversed string, and the length of the string is 10, then you need the first 4 characters of the string. You then use SUBSTRING
to extract the relevant part
A full example would be
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 an edit, that had some errors in the syntax, the below has functions to trim the start, and trim both sides of commas:
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);