CODEWITHSUNDEEP
sqlsql-server-2008
Shine
Shine

Reputation: 1423

'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?

Upvotes: 27

Views: 81862

Answers (5)

Nayanajith
Nayanajith

Reputation: 645

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

You can use this way also without schema :)

Upvotes: 25

Faraz Ali Siddiqui
Faraz Ali Siddiqui

Reputation: 301

//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

Upvotes: 30

OsQu
OsQu

Reputation: 1058

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

Upvotes: 0

DaveShaw
DaveShaw

Reputation: 52798

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)

Upvotes: 20

Datajam
Datajam

Reputation: 4241

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.

Upvotes: 0

Related Questions