See Also: Main_Page - Transact SQL Code Library - String Manipulation Functions

Trim patterns of a string

CREATE FUNCTION TRIM_STRING
               (@string      VARCHAR(8000),
                @trim_string VARCHAR(10))
RETURNS VARCHAR(8000)
AS
  BEGIN
    DECLARE  @output VARCHAR(8000)
    
    IF CHARINDEX(@trim_string,@string) = 0
      BEGIN
        RETURN 'the character(s) you requested to trim is not contained in the string'
      END
    
    WHILE CHARINDEX(@trim_string,@string) <> 0
      BEGIN
        SET @output = ''
        
        SELECT @output = @output + SUBSTRING(@string,1,(CHARINDEX(@trim_string,@string) - 1)) + SUBSTRING(@string,(CHARINDEX(@trim_string,@string) + LEN(@trim_string)),
                                                                                                          (LEN(@string) - (CHARINDEX(@trim_string,@string) + LEN(@trim_string)) + 1))
        
        SET @string = @output
      END
    
    RETURN @output
  END