22,209
社区成员
发帖
与我相关
我的任务
分享
/****** 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
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.