declare @a table(id int identity(1,1) ,a int)
declare @str varchar(100),@key varchar(100)
select @str='001,002,003,004,005,006',@key='001,002'
set @str=stuff(@str,1,len(@key)+1,'')insert @a(a) select top 100 0 from syscolumns
select @key+','+substring(@str+',',id,charindex(',',@str+',',id+1)-id) result
from @a
where substring(','+@str,id,1)=','
--result
/*result
-------------------
001,002,003
001,002,004
001,002,005
001,002,006(所影响的行数为 4 行)
*/
declare @str varchar(100),@key varchar(100)
select @str='001,002,003,004,005,006',@key='001,002'
set @str=stuff(@str,1,len(@key)+1,'')insert @a(a) select top 100 0 from syscolumns
select @key+','+substring(@str+',',id,charindex(',',@str+',',id+1)-id) result
from @a
where substring(','+@str,id,1)=','
--result
/*result
-------------------
001,002,003
001,002,004
001,002,005
001,002,006(所影响的行数为 4 行)
*/
DECLARE @key VARCHAR(800),@s VARCHAR(1000)
SET @key = '001,002'
SET @s = '001,002,003,004,005,006'SELECT s = A.[Key]+','+B.value
FROM
(
SELECT [key] = @key,
[s] = CONVERT(xml,
'<root><t>'+REPLACE(REPLACE(
@s,@key+',',''),
',','</t><t>')+'</t></root>')
) A
OUTER APPLY
(
SELECT value = N.t.value('.','varchar(100)')
FROM A.[s].nodes('/root/t') N(t)
) B
s
-------------------
001,002,003
001,002,004
001,002,005
001,002,006(4 行受影响)