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
as
begin
declare @i int; set @i = 1;
declare @j int; set @j = len(@text);
declare @u int;
while (@i < @j)
begin
set @u = unicode(substring(@text, @i, 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 @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
begin
declare @i int; set @i = 1
declare @j int; set @j = len(@text)
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
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