最近要在SQL用到正则表达式,google,发现2005和2008已经支持正则,
而且都有一些源码
/****** Object: UserDefinedFunction [dbo].[find_regular_expression] Script Date: 09/07/2009 13:30:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[find_regular_expression]
(
@source varchar(5000), --需要匹配的源字符串
@regexp varchar(1000), --正则表达式
@ignorecase bit = 0 --是否区分大小写,默认为false
)
RETURNS bit --返回结果0-false,1-true
AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer DECLARE @objMatches integer
DECLARE @results bit
EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END
但是我在使用过程中,发现效率很低,同样一个没有索引的字段,效率甚至比LIKE 还要瞒很多很多,select * from (
select top 5000 * from TB order by idx
) A WHERE dbo.find_regular_expression(A.ATTACKTYPE,'3208',0)=1
select * from (
select top 5000 * from TB order by idx
) A WHERE A.attacktype like '%3208%'
执行时间
SQL Server parse and compile time:
CPU time = 4 ms, elapsed time = 4 ms.(1 row(s) affected)SQL Server Execution Times:
CPU time = 5187 ms, elapsed time = 13108 ms.(1 row(s) affected)SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 51 ms.
大家可以看到正则用了13108 ms,而like只用了51ms,
大虾们有经验的来看看,这是怎么回事?
而且都有一些源码
/****** Object: UserDefinedFunction [dbo].[find_regular_expression] Script Date: 09/07/2009 13:30:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[find_regular_expression]
(
@source varchar(5000), --需要匹配的源字符串
@regexp varchar(1000), --正则表达式
@ignorecase bit = 0 --是否区分大小写,默认为false
)
RETURNS bit --返回结果0-false,1-true
AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer DECLARE @objMatches integer
DECLARE @results bit
EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END
但是我在使用过程中,发现效率很低,同样一个没有索引的字段,效率甚至比LIKE 还要瞒很多很多,select * from (
select top 5000 * from TB order by idx
) A WHERE dbo.find_regular_expression(A.ATTACKTYPE,'3208',0)=1
select * from (
select top 5000 * from TB order by idx
) A WHERE A.attacktype like '%3208%'
执行时间
SQL Server parse and compile time:
CPU time = 4 ms, elapsed time = 4 ms.(1 row(s) affected)SQL Server Execution Times:
CPU time = 5187 ms, elapsed time = 13108 ms.(1 row(s) affected)SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 51 ms.
大家可以看到正则用了13108 ms,而like只用了51ms,
大虾们有经验的来看看,这是怎么回事?
太多了.剪剪的blog就很多.
2005,2008直接使用clr来写正则函数.就更方便了
具体的还需要小梁来指点
小梁给个连接,thanks
自己去找
也就是SQL Server call外部dll,
但这个dll还是需要用.net来生成嘛。
问题来了,如果公司没有VS软件怎么办?我们公司都不让安装盗版软件的呀。
随SQL Server2008一起安装的framework3.5我看了一下,也没有
提供一个项目模板来建立用于数据库开发的项目。
因为就call函数而言,感觉理论上CLR call dll一样是调用dll里面的函数,
效率真的能快很多嘛?迷惑