TRIM is not a recognized built-in function name

Question

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

Solution 1

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

Solution 2

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

Solution 3

You can use this code for older versions:

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

Which results in 'JKL'

Comments

[Zohar Peled]

You might want to add the link to ALTER DATABASE (Transact-SQL) Compatibility Level. It has a list of supported compatibility levels for each SQL Server version.

[Nick.McDermaid]

You’re right that would be helpful. I did google this entire answer and hopefully the OP can do that himself! Feel free to edit!

[Nick.McDermaid]

But it's not an older version - it's apparently SQL 2017. Unless they got the client tool mixed up

[PSK]

It seems someone mistakenly tagged 2017 by reading the first comment from a different user. OP never mentioned it is 2017. So I am assuming it is some older version.

[Nick.McDermaid]

Ah I see. I assume the OP will never return to clear it up so I will see if can edit the question and remove it

[pixel]

Setting compatibility level errors with Msg 15048, Level 16, State 1, Line 4 Valid values of the database compatibility level are 100, 110, 120, or 130.

[pixel]

I am using MS SqlServer Management Studio 17.0 and 18.1 and both are having this issue. The COMPATIBILITY_LEVEL IS 130 but when I try to set it up to 140, I get error "Valid values of the database compatibility level are 100, 110, 120, or 130."

[Nick.McDermaid]

Then basically your installation doesn’t support that level. You need to install a newer version. You can see this in the link in my answer.

[Geoff]

@Nick.McDermaid When I test this on SQL 2017 with compatibility level 100, it works - I believe the compatibility level is not the cause, just the server version

[Nick.McDermaid]

I do appreciate any and all clarification supplied. There is a lot of confusion and activity on this abandoned question. At this stage no one knows the original SQL version or the compatibility level of the original question so who knows what the issue was.

[Geoff]

@Nick.McDermaid definitely a shame when a poster abandons the followup. Almost certainly they were on an earlier version than 2017 (but as you say, who knows!)

[Lam Le]

I'm an amateur in SQL. Although I have defined the function, I wonder why I cannot use TRIM as it always said 'trim' is not a recognized built-in function name. Turned out I have to use it like this dbo.TRIM.

[Christian]

And that's (and many many other things) we know that SQL Server really sucks! That's the way Microsoft does things: why make it simple if we can complicate it at all!