查询某个数在该数组中是否存在-函数

create function myaaaa1--查询某个数在该数组中是否存在
(
@a varchar(200), --数组
@i int                     --数字
)
returns int
as
begin
declare @result int;--结果
declare @left varchar(200);--,号左边数组
declare @h_left int;--左边数字——
declare @h_right int;


set @result=0;


while charindex(',',@a)>0--循环语句
begin
set @left=left(@a,charindex(',',@a)-1)
if charindex('-',@left)>0
begin--myaaaa
set @h_left=left(@left,charindex('-',@left)-1)
set @h_right=right(@left,len(@left)-charindex('-',@left))
select @result=case when  @i>@h_left-1 and @i<@h_right+1 then @result+1 
else @result end
end  
else
begin
select @result=case when convert(int,@left)=@i then @result+1 else @result end;
end 


set @a=right(@a,len(@a)-charindex(',',@a));
end 
if charindex('-',@a)>0
begin--myaaaa
set @h_left=left(@a,charindex('-',@a)-1)
set @h_right=right(@a,len(@a)-charindex('-',@a))
select @result=case when  @i>@h_left-1 and @i<@h_right+1 then @result+1 
else @result end
end  
else
begin
select @result=case when convert(int,@a)=@i then @result+1 else @result end;
end 
return @result;

end

select dbo.myaaaa1('4',4)

select dbo.myaaaa1('01,02,03,05,06,07,08,09,10,11,12,13,14,15,16',4)

select dbo.myaaaa1('5',4)
select dbo.myaaaa1('1,3-6',4)
select dbo.myaaaa1('3,9-12',4)
select dbo.myaaaa1('1,3-6,9-12',4)

select dbo.myaaaa1('3,5-6,9-12',4)

嘻嘻，小有得瑟。