Trim is not a recognized built-in function name

Detailed Exploration

Crafting a custom TRIM function

Upgrading isn't feasible for some systems. In that case, why not create a custom function:

CREATE FUNCTION dbo.TRIM(@string NVARCHAR(max)) 
RETURNS NVARCHAR(max) AS
BEGIN
    RETURN LTRIM(RTRIM(@string))
 -- Because best buds LTRIM and RTRIM are pretty close to being TRIM
END
GO


Invoke it like this:

SELECT dbo.TRIM(your_column) AS TrimmedColumn FROM your_table;
 -- getting TRIM'ed without needing a new SQL version, nice!


Diving deeper into TRIM, RTRIM, LTRIM

TRIM() is a handy function in SQL Server 2017 and onwards to remove both leading and trailing spaces. However, in earlier versions, you handle things differently with LTRIM() and RTRIM():

• LTRIM() is your go-to for eliminating leading spaces
• And RTRIM() can be counted on to knock off those pesky trailing spaces

The two combined can emulate TRIM() quite efficiently.

Other whitespace characters

Keep in mind LTRIM() and RTRIM() only deal with spaces. For other whitespace characters like tabs or newlines, you may need more complex logic like pattern matching or regular expressions.

Advantages of TRIM in SQL Server 2017+

On SQL Server 2017 and later where TRIM() is available, you can also remove specified characters beyond spaces. For example:

SELECT TRIM('x' FROM 'xxwordxx') AS TrimmedText; 
 -- Look mom, no 'x's!
