------------------------------------
-- Author: happyflystone
-- Date:2009-07-20
-- Parameter: @CardString
-- 被查询的串，形如：-13300001234,13300002230,13300002300
-- @CardNo 要查询的串
-- Return : int 0 -- 不存在于搜索串的范围内
-- 1 -- 存在于
-- 转载请注明出处。更多请访问：http://blog.csdn.net/happyflystone
-- 原帖地址：http://blog.csdn.net/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
--测试示例
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
*/