Stone Coast Web Design's Weblog

I was surprised to learn that both SQL Server 2000/2005 don't offer a built in TRIM function to remove leading or trailing whitespace from a string. What is possible, however, is to use a user defined function (UDF) to do the same thing.

SQL Server 2000:

CREATE FUNCTION dbo.TRIM(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN LTRIM(RTRIM(@string))
END

SQL Server 2005:

CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO

Then you call the function like this

SELECT DISTINCT(dbo.TRIM(Industry)) AS Industry FROM TCompanies
WHERE Industry IS NOT NULL