A表
aid name content
1 a aaa
2 b bbb
3 c ccc
4 d ddd
5 e eee
我传入一个字符串 a | b | a | e | b | c | " | " 风隔符号
我要根据传入字符串取A表中的值,但不能取重复的数据,就是传入上面的字符串,取的值为
1 a aaa
2 b bbb
3 c ccc
5 e eee
aid name content
1 a aaa
2 b bbb
3 c ccc
4 d ddd
5 e eee
我传入一个字符串 a | b | a | e | b | c | " | " 风隔符号
我要根据传入字符串取A表中的值,但不能取重复的数据,就是传入上面的字符串,取的值为
1 a aaa
2 b bbb
3 c ccc
5 e eee
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i int set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr) while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end if @inputstr <> '\'
insert @temp values(@inputstr) return
end
go --调用 declare @s varchar(1000) set @s='1,2,3,4,5,6,7,8,55' select * from dbo.fn_split(@s,',') drop function dbo.fn_split
go
create table #(aid int, name varchar(10), content varchar(10))
insert # select 1, 'a', 'aaa'
insert # select 2, 'b' , 'bbb'
insert # select 3, 'c' , 'ccc'
insert # select 4, 'd' , 'ddd'
insert # select 5, 'e' , 'eee'
declare @str varchar(50)
set @str='a|b|a|e|b|c'
select distinct * from # where charindex('|'+name+'|','|'+@str+'|')>0
/*aid name content
----------- ---------- ----------
1 a aaa
2 b bbb
3 c ccc
5 e eee(4 行受影响)*/
go
create table #(aid int, name varchar(10), content varchar(10))
insert # select 1, 'a', 'aaa'
insert # select 2, 'b' , 'bbb'
insert # select 3, 'c' , 'ccc'
insert # select 4, 'd' , 'ddd'
insert # select 5, 'e' , 'eee'
if object_id('dbo.fn_split')is not null drop function dbo.fn_split
go
create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
declare @str varchar(50)
set @str='a|b|a|e|b|c'
select distinct a.* from # a inner join dbo.fn_split(@str,'|')b
on a.name=b.a
/*aid name content
----------- ---------- ----------
1 a aaa
2 b bbb
3 c ccc
5 e eee(4 行受影响)*/
declare @tb table(aid int,name nvarchar(5),content nvarchar(5))
insert into @tb select 1,'a','aaa'
union all select 2,'b','bbb'
union all select 3,'c','ccc'
union all select 4,'d','ddd'
union all select 5,'e','eee'
declare @str nvarchar(100)
set @str='a|b|a|e|b|c|'
select * from @tb where CHARINDEX(name,@str)>0
1 a aaa
2 b bbb
3 c ccc
5 e eee