SQL Server has two built-in string trimming functions, LTRIM and RTRIM, these remove leading and trailing spaces respectively. There is no built-in function to remove arbitrary leading and/or trailing whitespace, including TABs, CR and/or LFs.

The built-in functions return the datatype of their input, whereas the UDFs below return explicit datatypes. I've put together a few variations of my trim whitespace function, one for non-unicode text, and a basic and advanced one for unicode text. For the sake of performance and convenience I've treated all control characters (0 - 31) as whitespace.

Note that from a performance point of view these UDFs will be considerably slower than the built-in functions so don't use them inside a large loop or when returning a large rowset unless you're sure you'll have non-space whitespace.

These functions are suitable for SQL Server 2005 and 2008 as well as SQL Server 2000 (just remove the RETURNS NULL ON NULL INPUT function option and convert the (max) types to a suitable fixed length type).

create function dbo.udf_TrimWhitespace(@text varchar(max)) returns varchar(max) with returns null on null input 
as
/*
** Return the input with leading and trailing whitespace removed.
*/
begin

   declare @i int; set @i = 1
   declare @j int; set @j = len(@text)

   while (ascii(substring(@text, @i, 1)) < 33 and @i < @j) set @i = @i + 1
   while (ascii(substring(@text, @j, 1)) < 33 and @j >= @i) set @j = @j - 1

   return substring(@text, @i, @j - @i + 1)

end
go

create function dbo.udf_NTrimWhitespace(@text nvarchar(max)) returns nvarchar(max) with returns null on null input 
as
/*
** Return the input with leading and trailing whitespace removed.
*/
begin

   declare @i int; set @i = 1
   declare @j int; set @j = len(@text) -- remember that len doesn't include trailing spaces.

   while (unicode(substring(@text, @i, 1)) < 33 and @i < @j) set @i = @i + 1
   while (unicode(substring(@text, @j, 1)) < 33 and @j >= @i) set @j = @j - 1

   return substring(@text, @i, @j - @i + 1)

end
go

create function dbo.udf_NTrimWhitespace(@text nvarchar(max)) returns nvarchar(max) with returns null on null input
/*
** Return the input with leading and trailing whitespace removed.
*/
as
begin

   declare @i int; set @i = 1;
   declare @j int; set @j = len(@text); -- remember that len doesn't include trailing spaces.
   declare @u int;

   while (@i < @j)
   begin
      set @u = unicode(substring(@text, @i, 1));
      if (@u > 0x0020 and @u not in ( 0x0085, -- NEL (control character next line)
                                      0x00A0, -- NBSP (NO-BREAK SPACE)
                                      0x1680, -- OGHAM SPACE MARK
                                      0x180E, -- MONGOLIAN VOWEL SEPARATOR
                                      0x2000, -- EN QUAD
                                      0x2001, -- EM QUAD
                                      0x2002, -- EN SPACE
                                      0x2003, -- EM SPACE
                                      0x2004, -- THREE-PER-EM SPACE
                                      0x2005, -- FOUR-PER-EM SPACE
                                      0x2006, -- SIX-PER-EM SPACE
                                      0x2007, -- FIGURE SPACE
                                      0x2008, -- PUNCTUATION SPACE
                                      0x2009, -- THIN SPACE
                                      0x200A, -- HAIR SPACE
                                      0x200B, -- ZERO WIDTH SPACE
                                      0x2028, -- LS (LINE SEPARATOR)
                                      0x2029, -- PS (PARAGRAPH SEPARATOR)
                                      0x202F, -- NNBSP (NARROW NO-BREAK SPACE)
                                      0x205F, -- MMSP (MEDIUM MATHEMATICAL SPACE)
                                      0x3000, -- IDEOGRAPHIC SPACE
                                      0xFEFF  -- ZERO WIDTH NO-BREAK SPACE
                                    )
         )
         break;
      set @i = @i + 1;
   end

   while (@j >= @i)
   begin
      set @u = unicode(substring(@text, @j, 1));
      if (@u > 0x0020 and @u not in (0x0085,0x00A0,0x1680,0x180E,0x2000,0x2001,0x2002,0x2003,0x2004,0x2005,0x2006,0x2007,0x2008,0x2009,0x200A,0x200B,0x2028,0x2029,0x202F,0x205F,0x3000,0xFEFF))
         break;
      set @j = @j - 1;
   end

   return substring(@text, @i, @j - @i + 1);

end
go