用字符串分解函数. /* 功能:实现split功能的函数 */create function dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) ) returns @temp table (a varchar(200)) as begin declare @i intset @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) endif @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 godeclare @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
额,我估计说的不明白: 大概是这么个需求: select * from table1 where field regexp (正则表达式) 如何写这个正则表达式呢?比如field是"4,12,13,15,16,19,20,21",我如何构造一个正则表达式匹配这个field呢?比如要匹配19,写成‘[^0-9]?19[^19]?’ 这样可以匹配,但是写‘[^0-9]?1[^0-9]?’怎么还是可以匹配呢????
/*
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @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)
endif @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 行)
*/按照这个分解函数,分解成两个临时表,然后用查询去判断.
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
godeclare @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
大概是这么个需求:
select * from table1 where field regexp (正则表达式)
如何写这个正则表达式呢?比如field是"4,12,13,15,16,19,20,21",我如何构造一个正则表达式匹配这个field呢?比如要匹配19,写成‘[^0-9]?19[^19]?’ 这样可以匹配,但是写‘[^0-9]?1[^0-9]?’怎么还是可以匹配呢????