TRIM is not a recognized built-in function name

For the following code:

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


select TRIM(@ss)

I've got an error:

'TRIM' is not a recognized built-in function name

91894 次浏览
小开
最佳答案

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
小开

You can use this code for older versions:

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

Which results in 'JKL'

小开

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


提交答案