日期:2014-05-18 浏览次数:20552 次
/* 功能:实现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 /* a ---------------------- 1 2 3 4 5 6 7 8 55 (所影响的行数为 9 行) */
------解决方案--------------------
create function dbo.f_str(@s1 varchar(100),@s2 varchar(100)) returns varchar(10) as begin declare @str varchar(10) set @str = '不匹配' while charindex(',',@s1) > 0 begin if charindex(','+ left(@s1 , charindex(',',@s1) - 1) + ',' , ',' + @s2 + ',') > 0 begin set @str = '匹配' BREAK end else set @s1 = substring(@s1 ,charindex(',' , @s1) + 1 , len(@s1)) end if charindex(','+ @s1 + ',' , ',' + @s2 + ',') > 0 set @str = '匹配' return @str end go declare @s1 as varchar(100) declare @s2 as varchar(100) set @s1 = '14,15,16' set @s2 = '2,3,4,14' select dbo.f_str(@s1 , @s2) /* ---------- 匹配 (所影响的行数为 1 行) */ set @s1 = '15,16' set @s2 = '2,3,4,14' select dbo.f_str(@s1 , @s2) /* ---------- 不匹配 (所影响的行数为 1 行) */ drop function dbo.f_str
------解决方案--------------------
这个序列不是固定的,能用正则的必须要有一定规则,否则用不了,
4|((1|2)[0-9])