34,838
社区成员




/*
功能:实现split功能的函数
*/
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 @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
如果只有三个,好办.
declare @s as varchar(100) '汽车,电脑,通讯'
declare @s1 as varchar(10)
declare @s2 as varchar(10)
declare @s3 as varchar(10)
set @s1 = left(@s , charindex(',' , @s) - 1)
set @s2 = substring(@s , charindex(',' , @s) + 1 , charindex(',' , @s , charindex(',' , @s) + 1) , charindex(',' , @s , charindex(',' , @s) + 1) - charindex(',' , @s) - 1)
set @s3 = reverse(left(reverse(@s) , charindex(',' , reverse(@s) - 1))
select * from tb where charindex(@s1 , col) > 0 or charindex(@s2 , col) > 0 or charindex(@s3 , col) > 0