--测试数据
create table ta(ids varchar(100))
create table tb(id int, value varchar(10))
go
insert ta select '1' union all select '2'
union all select '1,2' union all select '2,3'
union all select '3'
insert tb select '1', '张三'
union all select '2', '李四'
union all select '3', '王五'
go
--主要部分
create function fun(@str varchar(100))
returns varchar(100)
as
begin
set @str=','+@str+','
select @str=replace(@str,','+convert(varchar(10),id)+',',
','+value+',')
from tb
set @str=substring(@str, 2, len(@str)-2)
return @str
end
go
select dbo.fun(ids) from ta
--清除
drop function fun
drop table ta
drop table tb
create table ta(ids varchar(100))
create table tb(id int, value varchar(10))
go
insert ta select '1' union all select '2'
union all select '1,2' union all select '2,3'
union all select '3'
insert tb select '1', '张三'
union all select '2', '李四'
union all select '3', '王五'
go
--主要部分
create function fun(@str varchar(100))
returns varchar(100)
as
begin
set @str=','+@str+','
select @str=replace(@str,','+convert(varchar(10),id)+',',
','+value+',')
from tb
set @str=substring(@str, 2, len(@str)-2)
return @str
end
go
select dbo.fun(ids) from ta
--清除
drop function fun
drop table ta
drop table tb
create table 表a(a varchar(10))create table 表b([id] int,[name] varchar(10))
--插入数据
insert 表a select '1'
insert 表a select '2'
insert 表a select '1,2'insert 表b select 1,'张三'
insert 表b select 2,'李四'
--创建字符串连接函数
create function dbo.fn_link_name
(@a varchar(10))
returns varchar(100)
as
begin
declare @name_str varchar(100)
set @name_str=''
select @name_str=@name_str+[name]+','
from 表a a join 表b b
on a.[a] like '%'+cast(b.[id] as varchar(10))+'%'
where a.[a]=@a
set @name_str=left(@name_str,len(@name_str)-1)
return(@name_str)
end
--查询
select dbo.fn_link_name(a.[a])
from 表a a join 表b b
on a.[a] like '%'+cast(b.[id] as varchar(10))+'%'
group by a.[a]
--结果
/*
张三
张三,李四
李四(所影响的行数为 3 行)
*/
returns varchar(8000)
as
begin
declare @tmp varchar(8000)
set @tmp=''
while charindex(',',@s)>0
begin
select @tmp=@tmp+','+(select top 1 name from B where ID=convert(int,left(@s,charindex(',',@s)-1)))
set @s=stuff(@s,1,charindex(',',@s),'')
end
select @tmp=@tmp+','+(select top 1 name from B where ID=convert(int,@s))
return stuff(@tmp,1,1,'')
end
go--调用select dbo.f_str(字段) from A