Trimming a custom character from the end of a T-Sql string

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.

Databases: 

Comments

Hi, i tried to find what i am looking for.

I have all kind of values 10522, 7310, 2430 normal 5 or 4 digits. I want to remove the thrid (3) digit from the right only. So it will be 1022, 710, 230.

How to do that i am using sql and visual basic 

rahul's picture

1) Find string length, n (standard methods/properties available in practically all platforms).
2) If string length, n > 3 (and assuming 0-based indexing), do a substring as follows:
substring(0, n - 3) + substring(n-2)

That's the high-level pseudo-code, you can translate to the language of your choice.

Thanks extremely handy. Will certainly share website with my pals