Remove Leading and Trailing Spaces in SQL Server
-----------------------------------------------------------------
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_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
-------------------------------------------------------------------
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_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
--------------------------------------------------------------------
Source:http://www.rmjcs.com/SQLServer/TSQLFunctions/TrimWhitespace/tabid/829/Default.aspx
