MSSQL Trim Function

Trim() function is very popular function in many programming or sql languages which can delete leading or trailing spaces of any string. But Microsoft SQL Server does not support the function, so sad!

However, MS SQL Server has LTRIM() and RTRIM() functions which can delete leading and trailing respectively.

Example will show how to trim your string in MSSQL Server, it should works in 2000, 2005 and 2008 version

[sql] declare @str varchar(50)
set @str = ‘ ms sql trim function ‘
print @str
print ltrim(rtrim(@str))
[/sql]

Output:

[text] ms sql trim function
ms sql trim function
[/text]

Or you can implement a TRIM() function for your MS SQL Server database as below:

+ SQL Server 2000:

[sql] create function dbo.trim(@string varchar(8000))
returns varchar(8000)
begin
return ltrim(rtrim(@string))
end
[/sql]

+ SQL Server 2005 and SQL Server 2008:

[sql] create function dbo.trim(@string varchar(max))
returns varchar(max)
begin
return ltrim(rtrim(@string))
end
[/sql]

After that, run SQL script below to test:

[sql] declare @str varchar(50)
set @str = ‘ ms sql trim function ‘
select dbo.trim(@str)
[/sql]