Sample SQL Server Function to Trim a string

Below is a sample SQL Server function to Trim a string. It removes leading & trailing whitespace characters from a string.

This function differs from rtrim() and ltrim() in that it removes tab, newline, and carriage return characters in addition to spaces. Like ltrim() and rtrim(), if you pass null in, you get null back.

SQL Server Function :

create function [dbo].[TrimString]
(
    @Str varchar(max)
)
returns varchar(max)
as begin
    declare @First int = 1,
    @Last int = datalength(@Str)

    while ascii(substring(@Str, @First, 1)) in (32, 9, 10, 13)
    set @First += 1

    if @First > @Last begin
    -- the string is all whitespace (or empty)
    return ''
 end

    while ascii(substring(@Str, @Last, 1)) in (32, 9, 10, 13)
    set @Last -= 1

    return substring(@Str, @First, @Last - @First + 1)
end