How Ltrim/Rtrim/Replace works in Sql Server

YOU CANNOT USE TEMPORARY TABLE IN FUNCTION:

èI create a temporary table. And I was using that temporary table in function. I couldn’t use it. Then the table I have created below which will be used through out this topic.

TABLE CREATED

create table temp3

(

addressid int,

colony varchar(50),

subcolony varchar(100),

City varchar(50),

pincode int

)

insert into temp3

select 121,' sundi sai',' karan sai','Rgda',1000

union all select 122,'new colony','','Rgda',1001

union all select 123,' ','','',1002

union all select 124,'"newcolony"','','Rgda',1003

union all select 125,'      raniguda','    raniguda3','Rgda',1004

select * from temp3

FUNCTION CREATED

alter function [dbo].[fn_Get_trim]     

(     

   @address_id int

)  

returns varchar(500)

as

begin

declare @addressfinal varchar(500)

select

@addressfinal= coalesce(colony,subcolony,city) +' '+ convert(varchar,pincode)                           

from temp3

where

addressid=@address_id

return @addressfinal

end

select [dbo].[fn_Get_trim](121)

--As already we know the usage of coalesce. If colony is null then subcolony. If subcolony is null then city. But here colony is not null so we get colony alone.

 

select [dbo].[fn_Get_trim](125)

 

Look at this so much space .

LTRIM AND RTRIM COMES INTO PICTURE

alter function [dbo].[fn_Get_trim]     

(     

   @address_id int

)  

returns varchar(500)

as

begin

declare @addressfinal varchar(500)

select

@addressfinal= rtrim(LTRIM(coalesce(colony,subcolony,city))) +' '+ convert(varchar,pincode)                           

from temp3

where

addressid=@address_id

return @addressfinal

end

 

alter function [dbo].[fn_Get_trim]     

(     

   @address_id int

)  

returns varchar(500)

as

begin

declare @addressfinal varchar(500)

select

@addressfinal= Rtrim(LTRIM(isnull(colony,'')))+ ' ' +Rtrim(LTRIM(isnull(subcolony,'')))+' '+Rtrim(LTRIM(isnull(city,''))) +' '+ convert(varchar,pincode)                            

from temp3

where

addressid=@address_id

return @addressfinal

end

select [dbo].[fn_Get_trim](125)

o/p

 

--I don’t want this “”.  Now here where the Replace works.

@addressfinal= Rtrim(LTRIM(isnull(REPLACE(colony,'"',''),'')))+ ' ' +Rtrim(LTRIM(isnull(subcolony,'')))+' '+Rtrim(LTRIM(isnull(city,''))) +' '+ convert(varchar,pincode)                            

Now run it :

---------------------------------------------Cool J----------------------------------------------