SQL SERVER – TRIM() Function – UDF TRIM()

SQL Server does not have function which can trim leading or trailing spaces of any string. TRIM() is very popular function in many languages. SQL does have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively. I was expecting SQL Server 2005 to have TRIM() function. Unfortunately, SQL Server 2005 does not have that either. I have created very simple UDF which does the same work.

FOR SQL SERVER 2000:
CREATE FUNCTION dbo.TRIM(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO

FOR SQL SERVER 2005:
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO

Both the above UDF can be tested with following script
SELECT dbo.TRIM(' leading trailing ')
It will return string in result window as
'leading trailing'

Here is the quick video on the same subject:

There will be no spaces around them. It is very little but useful trick.

What Changed for the TRIM() Function in Newer Versions

Good news first. SQL Server 2017 added a built in TRIM, so on that version and later you do not need this UDF anymore. On older versions, LTRIM(RTRIM()) is still the answer.

A few details that trip people up:

• LTRIM and RTRIM remove only the space character. Tabs, line breaks and non-breaking spaces stay. For those, use REPLACE with CHAR(9), CHAR(10), CHAR(13) or CHAR(160) first.
• The built in TRIM also removes only spaces by default, unless you tell it which characters to remove.
• SQL Server ignores trailing spaces when it compares strings with =, so ‘abc’ and ‘abc ‘ count as equal. LEN ignores trailing spaces too, while DATALENGTH counts them.

One more tip about performance. If you wrap a column in a function inside a WHERE clause, SQL Server usually cannot seek an index on that column. It is better to clean the data once, when it is saved, and then compare the clean column directly.

If your code must run on old and new servers alike, keeping the UDF is fine. Just give it a name that cannot be confused with the built in function, so the next person who reads the code knows which one runs. Test it with an empty string and a NULL too: an empty string stays empty and NULL stays NULL.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.