I recently had a situation where I needed to trim a custom character from the right of a string in Sql Server. Although Sql Server provides a RTRIM function, it can only trim spaces from a string.

A quick search over web threw up some interesting results, but all of them were buggy in one way or another. The major problem was that most of them assumed that spaces would not occur in the string itself.

So, I came up with the following solution as a UDF:

    CREATE FUNCTION [dbo].[RTrimChar]
    (
        @input nvarchar(MAX),
        @trimChar nchar(1)
    )
    RETURNS nvarchar(MAX)
    AS
    BEGIN
    DECLARE @len int, @index int;
        
        set @len = LEN(@input);
        set @input = REVERSE(@input);
        set @index = PATINDEX('%[^' + @trimChar + ']%', @input);
        
        IF @index = 0
            set @input = ''
        ELSE
            set @input = REVERSE(SUBSTRING(@input, @index, @len));
            
        RETURN @input;
    END
    GO

    SELECT [CRC1].[dbo].[RTrimChar] (
       'Sample // String //',
      '/');
    GO

I would like to know if anyone faces any issues with the above solution for trimming any custom charcater from the right of an Sql string. It can be modified easily to do the same from the left of a string.