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