27,582
社区成员




create table tb(id int,name nvarchar(10))
insert into tb select 54321,'aaaaa'
insert into tb select 111111,'bbbbb'
go
declare @sid nvarchar(20)
set @sid='12345,54321,111111'
set @sid=@sid+','
select @sid=replace(@sid,convert(varchar,id)+',','') from tb
select left(@sid,len(@sid)-1)
go
drop table tb
/*
--------------------
12345
(1 行受影响)
*/
create table tb(id int,name nvarchar(10))
insert into tb select 54321,'aaaaa'
go
declare @sid nvarchar(20)
set @sid='12345,54321,111111'
set @sid=@sid+','
select @sid=replace(@sid,convert(varchar,id)+',','') from tb
select left(@sid,len(@sid)-1)
go
drop table tb
/*
--------------------
12345,111111
(1 行受影响)
*/
create table tb(id int,name nvarchar(10))
insert into tb select 12345,'aaaaa'
go
declare @sid nvarchar(20)
set @sid='12345,54321,111111'
set @sid=@sid+','
select @sid=replace(@sid,convert(varchar,id)+',','') from tb
select left(@sid,len(@sid)-1)
go
drop table tb
/*
--------------------
54321,111111
(1 行受影响)
*/
declare @table table (ID int,Name varchar(4))
insert into @table
select 12345,'例子'
declare @parm varchar(20)
set @parm='12345,54321,111111'
select * from @table where
','+@parm+',' like '%,'+cast(ID as varchar(20))+',%'
/*
ID Name
----------- ----
12345 例子
*/
/*按照符号分割字符串*/
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(200))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
select * from dbo.m_split('12345,54321,111111',',')
/*
col
-----------
12345
54321
111111
*/