CODEWITHSUNDEEP
sqlsql-server-2008trim
Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

TrimEnd Equivalent in SQL Server

I have column (Numbers) which has values as follows:

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

I want to Trim all the Commas at the end of string, So that result would be

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

I have tried below Query but by this we can remove only 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 can I remove all the commas at the end of string?

Upvotes: 11

Views: 12109

Answers (6)

Muthukumar S
Muthukumar S

Reputation: 11

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

Upvotes: 1

user5497879
user5497879

Reputation: 1

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,

Upvotes: 0

GarethD
GarethD

Reputation: 69749

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);

Upvotes: 10

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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)

Upvotes: 2

Disha
Disha

Reputation: 392

Run below query and get 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)

Upvotes: 0

Rhys Jones
Rhys Jones

Reputation: 5498

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

Upvotes: 2

Related Questions