[MSSQL]检查给定串是否存在于由区间及点集的结合内

------------------------------------ -- Author: happyflystone -- Date:2009-07-20 -- Parameter: @CardString -- 被查询的串，形如：-13300001234,13300002230,13300002300 -- @CardNo 要查询的串 -- Return : int 0 -- 不存在于搜索串的范围内 -- 1 -- 存在于 -- 转载请注明出处。更多请访问： http://blog.youkuaiyun.com/happyflystone -- 原帖地址： http://blog.youkuaiyun.com/happyflystone/archive/2009/07/21/4365264.aspx ------------------------------------ --创建函数 Create function IsInCardString(@CardString varchar(8000),@CardNo varchar(11)) returns int as begin declare @temp table(a varchar(200)) declare @i int set @CardString = rtrim(ltrim(@CardString))+',' set @i = charindex(',', @CardString) while @i >= 1 begin insert @temp values(left(@CardString, @i - 1)) set @CardString = substring(@CardString, @i + 1, len(@CardString) - @i) set @i = charindex(',', @CardString) end if exists(select 1 from ( select case when charindex('-',a) > 0 then left(a,11) else a end as s, case when charindex('-',a) > 0 then right(a,11) else a end as e from @temp ) a where @CardNo between s and e) set @i= 1 else set @i= 0 return @i end go --测试示例 declare @CardString varchar(1000) set @CardString ='13300000000-13300001234,13300002230,13300002300,13300002302,13300004101-13300004204,13300004212,13300004310' declare @CardNo varchar(1000) set @CardNo = '13300000001' --存在 select dbo.IsInCardString(@CardString,@CardNo) as result1 set @CardNo = '13300001235' --不存在 select dbo.IsInCardString(@CardString,@CardNo) as result2 --运行结果 /* result1 ----------- 1 result2 ----------- 0 */