TRIM is not a recognized built-in function name

TRIM is introduced in SQL Server (starting with 2017).

In older version of SQL Server to perform trim you have to use LTRIM and RTRIM like following.

DECLARE @ss varchar(60)
  SET @ss = ' admin '

  select RTRIM(LTRIM(@ss))

If you don't like using LTRIM, RTRIM everywhere, you can create your own custom function like following.

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

Perhaps it's set to an earlier compatibility level.

Use this to find out:

SELECT compatibility_level FROM sys.databases

SQL Server 2017 is 140

If it's any lower then it won't recognize TRIM

To change the compatibility to SQL Server 2017

ALTER DATABASE database_name   
SET COMPATIBILITY_LEVEL = 140

For a list of supported compatibility levels for each SQL Server version check out ALTER DATABASE (Transact-SQL) Compatibility Level.

Based on comments below, your installed version of SQL Server is SQL 2016. You need to install SQL Sever 2017 to get TRIM

You can use this code for older versions:

SELECT RTRIM (LTRIM (' JKL ')) AS Trimmed

Which results in 'JKL'

Related