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----------------------------------------------
No comments:
Post a Comment