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

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

I am executing in the below way.

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

I am getting the following error.

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

Could any one please help me find the issue?

Answers


You need to use the Schema prefix when calling user defined functions. In your case this will most likely be "dbo".

Change your select statement to:

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

//use RTrim instead of Trim sql 2008

RTrim(ColumnName)

like this

select RTrim(a.ContactName) + ' ' + RTrim(a.City) as Name_City from customers as a

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

You can use this way also without schema :)


SQL server tries to parse TRIM as a built-in function. To call user-defined function you should put schema prefix in front of the function call. Try something like:

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

Since dbois a default schema prefix.

If you want to change your schema, declare the function as follow: (note the schema prefix in front of function name)

create function dbo.TRIM(@data varchar(20)) returns varchar(100)
as
begin
   --function body
end

The error is that 'TRIM' is not a built-in function in SQL Server (as the error message suggests :) )

You can either wrap it with both LTRIM and RTRIM instead, or create your own TRIM function that does the same.


Need Your Help

How to enable grey rectangular backgrounds on mouseover similar to Office 365?

javascript html css

I got a fiddle http://jsfiddle.net/HEue6/1/ that looks good for a start and now I want it to look and feel more like Office 365. I think the font in Office 365 is Segoe UI Semilight but I'm not sur...

Hide MySQL count column

mysql sql database count aggregate-functions

When you use count and group by in mysql, how do I hide the count column that implicitly shows up in the resulting table?