create table tab1(CompanyPropertyID varchar(10),CompanyPropertyName varchar(100))
Insert into tab1
select '01','aaa'
union all select '02','bbb'
union all select '03','ccc'
create table tab2(SellID varchar(10),CompanyPropertyIDS varchar(100))
Insert into tab2
select 'A001','01;02;'
union all select 'A001','01'
union all select 'A001','01;03;'
union all select 'A001','01;02;03;'
select * from tab1
select * from tab2
--創建函數處理
create function dbo.fn_f(@s varchar(1000))
returns varchar(1000)
as
begin
declare @a varchar(1000)
declare @i int
set @a=''
set @i=1
while @i>0
begin
select @a=@a+CompanyPropertyName+';' from tab1 where CompanyPropertyID=substring(@s,@i,2)
set @i=charindex(';',@s,@i+3)-2
end
return(left(@a,len(@a)-1))
end
--刪除
drop table tab1
drop table tab2
drop function dbo.fn_f