sql server 正则匹配查询数据 

 1 IF OBJECT_ID (N'dbo.RegexMatch') IS NOT NULL
 2    DROP FUNCTION dbo.RegexMatch
 3 GO
 4 ALTER FUNCTION dbo.RegexMatch
 5     (
 6       @pattern VARCHAR(2000),
 7       @matchstring VARCHAR(MAX)--Varchar(8000) got SQL Server 2000
 8     )
 9 RETURNS INT
10 /* The RegexMatch returns True or False, indicating if the regular expression matches (part of) the string. (It returns null if there is an error).
11 When using this for validating user input, you'll normally want to check if the entire string matches the regular expression. To do so, put a caret at the start of the regex, and a dollar at the end, to anchor the regex at the start and end of the subject string.
12 */ 
13 AS BEGIN
14 
15     DECLARE @objRegexExp INT,
16         @objErrorObject INT,
17         @strErrorMessage VARCHAR(255),
18         @hr INT,
19         @match BIT 
20 
21      
22     SELECT  @strErrorMessage = 'creating a regex object'
23     EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
24     IF @hr = 0 
25         EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
26         --Specifying a case-insensitive match 
27     IF @hr = 0 
28         EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
29         --Doing a Test' 
30     IF @hr = 0 
31         EXEC @hr= sp_OAMethod @objRegexExp, 'Test', @match OUT, @matchstring
32     IF @hr <> 0 
33         BEGIN
34             RETURN NULL
35         END
36     EXEC sp_OADestroy @objRegexExp
37     RETURN @match
38    END
39 GO
40 
41 --修改变量启用
42 sp_configure 'show advanced options', 1;
43 GO
44 RECONFIGURE;
45 GO
46 sp_configure 'Ole Automation Procedures', 1;
47 GO
48 RECONFIGURE;
49 GO
50 
51 EXEC sp_configure 'Ole Automation Procedures';
52 GO 
53 
54 
55 --测试 匹配两个相同的单词
56 SELECT dbo.RegexMatch('("([A-Za-z0-9]+) +\1\b)','"test test asdas')

 

posted @ 2021-03-26 15:09  方金  阅读(1186)  评论(0)  编辑  收藏  举报