--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] GO create table [TB]([bm] varchar(2),[mc] varchar(5)) insert [TB] select 'a1','药品1' union all select 'b2','药品2' union all select 'c3','药品'DECLARE @str VARCHAR(200) SET @str= 'a1,b2,c3'SELECT @str=REPLACE(@str,bm,mc) FROM TB tSELECT @str drop table [TB]
create table xv(bm varchar(10),mc varchar(10))insert into xv(bm,mc) select 'a1', '药品1' union all select 'b2', '药品2' union all select 'c3', '药品3' declare @x varchar(20) select @x='a1,b2,c3'select stuff( (select ','+d.mc from (select substring(a.x,b.number,charindex(',',a.x+',',b.number)-b.number) 'x' from (select @x x) a,master.dbo.spt_values b where b.type='P' and b.number between 1 and len(a.x) and substring(','+a.x,b.number,1) = ',') c inner join xv d on c.x=d.bm for xml path('')),1,1,'') 'y'/* y ------------------------- 药品1,药品2,药品3(1 row(s) affected) */
方法2,create table xv(bm varchar(10),mc varchar(10))insert into xv(bm,mc) select 'a1', '药品1' union all select 'b2', '药品2' union all select 'c3', '药品3' declare @x varchar(20),@sql varchar(6000) select @x='a1,b2,c3'select @sql='replace('+isnull(@sql,''''+@x+'''')+','''+bm+''','''+mc+''')' from xvselect @sql='select '+@sql+ ' ''y'''exec(@sql)/* y ------------------------- 药品1,药品2,药品3(1 row(s) affected) */
SELECT @str=REPLACE(','+@str+',',','+bm+',',mc) FROM TB t这样就可以了
if object_id('[TB]') is not null drop table [TB]
GO
create table [TB]([bm] varchar(2),[mc] varchar(5))
insert [TB]
select 'a1','药品1' union all
select 'b2','药品2' union all
select 'c3','药品'DECLARE @str VARCHAR(200)
SET @str= 'a1,b2,c3'SELECT @str=REPLACE(@str,bm,mc) FROM TB tSELECT @str
drop table [TB]
create table xv(bm varchar(10),mc varchar(10))insert into xv(bm,mc)
select 'a1', '药品1' union all
select 'b2', '药品2' union all
select 'c3', '药品3'
declare @x varchar(20)
select @x='a1,b2,c3'select stuff(
(select ','+d.mc
from
(select substring(a.x,b.number,charindex(',',a.x+',',b.number)-b.number) 'x'
from (select @x x) a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.x)
and substring(','+a.x,b.number,1) = ',') c
inner join xv d on c.x=d.bm
for xml path('')),1,1,'') 'y'/*
y
-------------------------
药品1,药品2,药品3(1 row(s) affected)
*/
select 'a1', '药品1' union all
select 'b2', '药品2' union all
select 'c3', '药品3'
declare @x varchar(20),@sql varchar(6000)
select @x='a1,b2,c3'select @sql='replace('+isnull(@sql,''''+@x+'''')+','''+bm+''','''+mc+''')'
from xvselect @sql='select '+@sql+ ' ''y'''exec(@sql)/*
y
-------------------------
药品1,药品2,药品3(1 row(s) affected)
*/
SELECT @str=REPLACE(','+@str+',',','+bm+',',mc) FROM TB t这样就可以了