How to fix the ”’trim’ is not a recognized built-in function name.” error?

Question

Asked: July 30, 20222022-07-30T03:41:18+00:00 2022-07-30T03:41:18+00:00In: Error

. Advertisement .

..3..

. Advertisement .

..4..

I still get the error ”‘trim’ is not a recognized built-in function name.” while running my program.

I have created simple function

create function TRIM(@data varchar(20)) returns varchar(100)
as
begin
  declare @str varchar(20)
  set @str = rtrim(ltrim(@data))
  return @str
end

Then I am executing in the following way.

declare @s varchar(25)
set @s = '      Amru '
select TRIM(@s)

And then it returns me the error message:

Msg 195, Level 15, State 10, Line 3
'TRIM' is not a recognized built-in function name.

I don’t know what’s wrong. Can you help me?

You must login to add an answer.

Need An Account,

hdtutoria · Accepted Answer · 2022-07-30T03:48:56+00:00

The cause: This error happens because TRIM is introduced in SQL Server and can’t be used in older version of SQL Server.

Solution: You need to use LTRIM and RTRIM as the following:

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

  select RTRIM(LTRIM(@ss))

Or you can create your own custom function like following if you don’t like using LTRIM, RTRIM everywhere.

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