if object_id('test') is not null drop table test select 47 as userid, cast('39,34,21,58' as varchar(50)) as roleid into test union select 48, '35,34,58' ------------------------------------------------- if object_id('testfunc') is not null drop function testfunc go create function testfunc(@fromid int, @toid int) returns varchar(50) as begin declare @s1 varchar(50), @s2 varchar(50) declare @n int select @s1 = roleid + ',' from test where userid = @fromid select @s2 = roleid from test where userid = @toid set @n = charindex(',', @s1) while @n > 0 begin if charindex(',' + left(@s1, @n), ',' + @s2 + ',') = 0 set @s2 = @s2 + ',' + left(@s1, @n - 1) set @s1 = right(@s1, len(@s1) - @n) set @n = charindex(',', @s1) end return(@s2) end go update test set roleid = dbo.testfunc(48, userid) where userid = 47--如果有必要就添加下面的语句 update test set roleid = '' where userid = 48select * from test /* userid roleid 47 39,34,21,58,35 48 */ drop function testfunc ------------------------------------------------- drop table test
declare @str1 nvarchar(1000), @str2 nvarchar(1000) select @str1='39,34,21,58', @str2='100,35,34,58,45,12,21' select @str1=@str1+',', @str2=@str2+',' while len(@str2)-len(replace(@str2,',',''))+1>1 begin if charindex(left(@str2,charindex(',',@str2)-1)+',',@str1)=0 begin set @str1=@str1+left(@str2,charindex(',',@str2)-1)+',' end set @str2=replace(@str2,left(@str2,charindex(',',@str2)-1)+',','') end print reverse(stuff(reverse(@str1),1,1,''))结果39,34,21,58,100,35,45,12
select 47 as userid, cast('39,34,21,58' as varchar(50)) as roleid
into test
union select 48, '35,34,58'
-------------------------------------------------
if object_id('testfunc') is not null drop function testfunc
go
create function testfunc(@fromid int, @toid int) returns varchar(50)
as
begin
declare @s1 varchar(50), @s2 varchar(50)
declare @n int
select @s1 = roleid + ',' from test where userid = @fromid
select @s2 = roleid from test where userid = @toid
set @n = charindex(',', @s1)
while @n > 0
begin
if charindex(',' + left(@s1, @n), ',' + @s2 + ',') = 0
set @s2 = @s2 + ',' + left(@s1, @n - 1)
set @s1 = right(@s1, len(@s1) - @n)
set @n = charindex(',', @s1)
end
return(@s2)
end
go
update test
set roleid = dbo.testfunc(48, userid)
where userid = 47--如果有必要就添加下面的语句
update test set roleid = '' where userid = 48select * from test
/*
userid roleid
47 39,34,21,58,35
48
*/
drop function testfunc
-------------------------------------------------
drop table test
select @str1='39,34,21,58',
@str2='100,35,34,58,45,12,21'
select @str1=@str1+',', @str2=@str2+','
while len(@str2)-len(replace(@str2,',',''))+1>1
begin
if charindex(left(@str2,charindex(',',@str2)-1)+',',@str1)=0
begin
set @str1=@str1+left(@str2,charindex(',',@str2)-1)+','
end
set @str2=replace(@str2,left(@str2,charindex(',',@str2)-1)+',','')
end
print reverse(stuff(reverse(@str1),1,1,''))结果39,34,21,58,100,35,45,12