--这样的数据库设计,很不好。Create table A(id int, nameIDs varchar(50)) insert into A select 1,',10,11,12,' GOdeclare @input varchar(50) declare @return varchar(50) set @return='' set @input =',11,9,13,12,14,'select @return =@return+','+X.nameIDs from ( SELECT SUBSTRING(stuff(@input,1,1,''),B.number,CHARINDEX(',',stuff(@input,1,1,'')+',',B.number)-B.number) AS [nameIDs] from master.dbo.spt_values AS B where B.type='p' AND B.number BETWEEN 1 AND LEN(@input)-2 AND SUBSTRING(@input,B.number,1)=',' ) X , A where charindex(','+X.nameIDs+',', A.nameIDs)<=0select @return /* ,9,13,14*/ Drop table A
--这样的数据库设计,很不好。Create table A(id int, nameIDs varchar(50))
insert into A select 1,',10,11,12,'
GOdeclare @input varchar(50)
declare @return varchar(50)
set @return=''
set @input =',11,9,13,12,14,'select @return =@return+','+X.nameIDs
from
(
SELECT
SUBSTRING(stuff(@input,1,1,''),B.number,CHARINDEX(',',stuff(@input,1,1,'')+',',B.number)-B.number) AS [nameIDs]
from
master.dbo.spt_values AS B
where B.type='p' AND B.number BETWEEN 1 AND LEN(@input)-2
AND SUBSTRING(@input,B.number,1)=','
) X , A
where charindex(','+X.nameIDs+',', A.nameIDs)<=0select @return
/*
,9,13,14*/
Drop table A