create table t1(n varchar(10),p nvarchar(30))
insert t1
select N'張三', 'a,b,h'
union select N'李四', 'i,e,c,b'
create table t2(code varchar(10),n1 nvarchar(30))
insert t2
select 'a' , '上海'
union select 'b' , N'北京'
union select 'c' , N'成都'
union select 'd' , N'長沙'
union select 'e' , N'深圳'
union select 'i' , N'廣州'
union select 'h' , N'香港'
go
create function tf(@id varchar(30))
returns nvarchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+','+n1 from t2 where charindex(code,@id)>0
set @str=stuff(@str,1,1,'')
return @str
end
go
select n,p=dbo.tf(p) from t1
drop function tf
drop table t1,t2
