TSQL正则表达式工作台

TSQL 正则表达式工作台
2007.11.27
作　者：Robyn Page and Phil Factor 译者：rex.zhasm
源代码：RegexWorkbench.sql RegexWorkbench2000.sql

/*本工作台主要讲述如何通过TSQL语句在SQL Server中使用正则表达式。至于正则表达式如何工作，如何将其联接起来，则不在本文的讨论范围，因为网上此类教程不胜枚举。如果您没在SQL Server中使用过正则式，本文就为您展示其豹之一斑，并建议您动手测试。

如果您与Phil、与我一样，都是没有超能力的凡人，我们建议您还是使用像RegexBuddy(http://www.regexbuddy.com)这样的工具，来构造、编辑和解析正则表达式。为了让手头只有SQL Server 2000的人也能使用此工作台，我们在例子中使用了OLE，但也适用于CLR(公共语言运行库)。

本文的源代码附在“作者”下面一行。*/

/*

-- 内　　容 --

1. 简介
   
2. OLE函数
   
   1. OLE正则式匹配函数
      
   2. OLE正则式匹配函数
      
   3. OLE正则式查找（执行）函数
      
3. 合并两条正则式
   
4. OLE正则式的性能
   

 

 

正则表达式对于数据库程序员来说非常有用，尤其是在数据验证，data feeds 以及数据转换中。在大多数情况下，使用grep、awk或Funduc的S&R（搜索与替换工具）来处理正则表达式才最为得心应手。但是，有时在TSQL中使用正则表达式也能一样顺手，稍后就会看到。

正则表达式是不规则的。我们指的是，不存在一种能通用于所有正则引擎的正则表达式方言。与此相反，正则表达式并非总是绿色便携的，多种方言并行于世，相似而不兼容，例如Perl5.8，Java.util.regex，.NET，PHP，Python，Ruby，ECMA Javascript，PCRE，Apache，vi，TCL ARE Shell 工具，POSIX BRE(Basic Regular Expressions)，Funduc和JGsoft。[译注：JGsoft旗下的powergrep是笔者在windows下最喜爱的正则表达式工具。]

轻松易懂从来不是正则表达式的特性。它是一系列速记手法的组合。乍一看，正则表达式就像是有人在键盘上重复坐下形成的乱码。即使在解析时，其逻辑也难一目了然。如果您不服，那请解释一下这条正则式吧！

 

网上最好的正则表达式教程，或许在这里www.regular-expressions.info。但是这篇也值得一读，使用正则表达式校验实际数据，算做是正则表达式的介绍。

使用GREP和AWK之类的命令行正则表达式工具就能处理很多事情。然而，在TSQL中直接使用正则式更为便捷。在SQL Server中有两种正则式引擎，分别为：

• .NET 正则式，包含于system.text.regularexpression模块中。
• VBScript.RegExp的ECMA正则式，与IE浏览器一同发布，用于Javascript和JScript。

 

两者都是绝佳的标准正则表达式工具，在TSQL中都表现出色。

.NET Regex需要CLR函数的支持，仅能运行于SQL Server 2005，（以及2007）。请参阅Christoffer Hedgate撰写的“CLR集成”一文。

ECMA Regex 可以通过VBScript.RegExp来使用，这在SQL Server 2000也能用。其正则式与Javascript兼容。

使用CLR的优势是，NET框架下的正则表达式非常优秀，运行良好。但是，虽然其技术已广为人知，VBScript.RegExp强大的用法早就深入人心。因此，本工作台将侧重于后者。

OLE函数
------------------

请留意这些函数的许多属性:

IgnoreCase 忽略大小写

正则表达式在默认情况下是大小写敏感的。在下面的函数中，我们将IgnoreCase属性设为'真'来忽略大小写。

The Multiline property 多行属性

行首定位锚'^'与行尾定位美元符'$'在默认情况下仅仅匹配目标字串的行首位置与行尾位置。如果您的目标字串包含以换行符分隔的多行文本，可以将Multiline属性设置为真，以便让行首定位锚与美元符匹配这些文本的开头和结尾。（没有任何选项能让点号'.'匹配换行符。）

The Global property 全局属性

如果需要RegExp对象返回或替换所有的匹配，而不仅仅是第一个，请设置全局属性为真。

在第一个函数中，只有IgnoreCase才是相关的，我们将此可变变量强行设置为1。这是因为，大小写敏感的搜索毕竟是少数。

OLE正则式匹配函数
-----------------------------

以简单例子作为开始，这是一个使用正则表达式匹配测试一个字串的例子*/

IF OBJECT_ID (N'dbo.RegexMatch') IS NOT NULL
DROP FUNCTION dbo.RegexMatch
GO
CREATE FUNCTION dbo.RegexMatch
(
@pattern VARCHAR(2000),
@matchstring VARCHAR(MAX)--Varchar(8000) got SQL Server 2000
)
RETURNS INT

/* RegexMatch返回真或假，来表示正则表达式是否匹配该字串（或其一部分）。（如果返回null则表示产生错误。）

使用它来校验用户输入时，经常需要检验用户所输入的整行文本是否匹配正则表达式。这时，要在正则式行首加上行首定位锚^，行尾加上美元符，使正则式能定位于目标字串的行首与行尾。
*/

AS BEGIN
DECLARE @objRegexExp INT,
@objErrorObject INT,
@strErrorMessage VARCHAR(255),
@hr INT,
@match BIT

SELECT @strErrorMessage = 'creating a regex object'
EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
--Specifying a case-insensitive match 指定忽略大小写的匹配
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
--Doing a Test' 做出测试
IF @hr = 0
EXEC @hr= sp_OAMethod @objRegexExp, 'Test', @match OUT, @matchstring
IF @hr <>0
BEGIN
RETURN NULL
END
EXEC sp_OADestroy @objRegexExp
RETURN @match
END
GO

/*现在，使用该过程，我们可以做一些复杂的输入校验了。*/

--是否存在重复单词
SELECT dbo.RegexMatch('\b(\w+)\s+\1\b','this has has been repeated')--1
SELECT dbo.RegexMatch('\b(\w+)\s+\1\b','this has not been repeated')--0

--查找与一个词邻近的另一个词。（在本例子中，是查找相隔一个或二个单词的'for'和'last'）
SELECT dbo.RegexMatch('\bfor(?:\W+\w+){1,2}?\W+last\b',
'You have failed me for the last time, Admiral')--1
SELECT dbo.RegexMatch('\bfor(?:\W+\w+){1,2}?\W+last\b',
'You have failed me for what could be the last time, Admiral')--0

--是否是合法的信用卡号
SELECT dbo.RegexMatch('^(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6011[0-9]{12}|3(?:0
[0-5]|[68][0-9])[0-9]{11}|3[47][0-9]{13}|(?:2131|1800)\d{11})$','4953129482924435')

--这是合法的ZIP编码吗？
SELECT dbo.RegexMatch('^[0-9]{5,5}([- ]?[0-9]{4,4})?$','02115-4653')

--这是合法的邮政编码吗？
SELECT dbo.RegexMatch('^([Gg][Ii][Rr] 0[Aa]{2})|((([A-Za-z][0-9]{1,2})|(([A-Za-z][A-Ha
-hJ-Yj-y][0-9]{1,2})|(([A-Za-z][0-9][A-Za-z])|([A-Za-z][A-Ha-hJ-Yj-y][0-9]?[A-Za-z])))
) {0,1}[0-9][A-Za-z]{2})$','RG35 2AQ')

--这是合法的欧洲日期吗？
SELECT dbo.RegexMatch('^((((31\/(0?[13578]|1[02]))|((29|30)\/(0?[1,3-9]|1[0-2])))\/(1[
6-9]|[2-9]\d)?\d{2})|(29\/0?2\/(((1[6-9]|[2-9]\d)?(0[48]|[2468][048]|[13579][26])|((16
|[2468][048]|[3579][26])00))))|(0?[1-9]|1\d|2[0-8])\/((0?[1-9])|(1[0-2]))\/((1[6-9]|[2
-9]\d)?\d{2})) (20|21|22|23|[0-1]?\d):[0-5]?\d:[0-5]?\d$','12/12/2007 20:15:27')

--这是合法的货币值（美元）吗？
SELECT dbo.RegexMatch('^\$(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$','$34,000.00')

--这是合法的货币值（英镑）吗？
SELECT dbo.RegexMatch('^\£(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$',
'£34,000.00')

--合法的邮箱地址？
SELECT dbo.RegexMatch('^(([a-zA-Z0-9!#\$%\^&\*\{\}''`\+=-_\|/\?]+(\.[a-zA-Z0-9!#\$%\^&
\*\{\}''`\+=-_\|/\?]+)*){1,64}@(([A-Za-z0-9]+[A-Za-z0-9-_]*){1,63}\.)*(([A-Za-z0-9]+[A
-Za-z0-9-_]*){3,63}\.)+([A-Za-z0-9]{2,4}\.?)+){1,255}$','Phil.Factor@simple-Talk.com')

/*

在该函数的返回机制对于产生的错误会优先返回。如果产生了OLE错误，就会返回null值。

还有另外两则可用的正则式函数。使用它们，您能够在TSQL中任何地方使用正则表达式，而不必调用笨拙的OLE接口。

OLE正则式替换函数
-----------------------------

*/

IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL
DROP FUNCTION dbo.RegexReplace
GO
CREATE FUNCTION dbo.RegexReplace
(
@pattern VARCHAR(255),
@replacement VARCHAR(255),
@Subject VARCHAR(MAX),
@global BIT = 1,
@Multiline bit =1
)
RETURNS VARCHAR(MAX)

/*RegexReplace函数需要三个字串参数：模式（正则表达式），替换表达式，以及需要处理的目标字串。

替换表达式是难点。您可以使用空字串 '' 作为替换的文本@replacement。其结果是，目标字串中的所有匹配部分被删除，再将剩余的字串返回。

如果把匹配部分作为替换字串的一部分插入到原字串，直接在替换文本中加入$&即可。（请参考下面的“查找#注释并添加TSQL --”部分）

如果正则式中包含捕获括号，则可以在替换字串中使用回溯。在替换字串中，$1插入第一组捕获，$2插入第二组，依次类推，直到$9。（举例见下面的“将已分隔的文本导入数据库”部分）。如需在替换文本中插入美元符文本，只要在传递给Replace函数的字串中包含两个连续美元符即可。*/

AS BEGIN
DECLARE @objRegexExp INT,
@objErrorObject INT,
@strErrorMessage VARCHAR(255),
@Substituted VARCHAR(8000),
@hr INT,
@Replace BIT

SELECT @strErrorMessage = 'creating a regex object'
EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
IF @hr = 0
SELECT @strErrorMessage = 'Setting the Regex pattern',
@objErrorObject = @objRegexExp
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
IF @hr = 0 /*默认情况下正则表达式是大小写敏感的。将IgnoreCase设置为真时，则为忽略大小写模式 */
SELECT @strErrorMessage = 'Specifying the type of match'
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
IF @hr = 0
SELECT @strErrorMessage = 'Doing a Replacement'
IF @hr = 0
EXEC @hr= sp_OAMethod @objRegexExp, 'Replace', @Substituted OUT,
@subject, @Replacement
/*如果RegExp.Global属性为假，Replace会使用替换字串来替换掉@subject字串中的第一个匹配（如果存在的话），然后返回换后的@Subject。如果RegExp.Global为真，则@Subject中的所有匹配都将被替换，然后返回替换后的@Subject。*/
IF @hr <>0
BEGIN
DECLARE @Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT

EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
@Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
SELECT @strErrorMessage = 'Error whilst '
+ COALESCE(@strErrorMessage, 'doing something') + ', '
+ COALESCE(@Description, '')
RETURN @strErrorMessage
END
EXEC sp_OADestroy @objRegexExp
RETURN @Substituted
END
GO
--删除文本中的重复单词
SELECT dbo.RegexReplace('\b(\w+)(?:\s+\1\b)+', '$1',
'Sometimes I cant help help help stuttering',1, 1)

--查找#注释，并添加TSQL --
SELECT dbo.RegexReplace('#.*','--$&','
# this is a comment
first,second,third,fourth',1,1)

--将URL替换为HTML格式
SELECT dbo.RegexReplace(
'\b(https?|ftp|file)://([-A-Z0-9+&@#/%?=~_|!:,.;]*[-A-Z0-9+&@#/%=~_|])',
'<a href="$2">$2</a>',
'There is this amazing site at http://www.simple-talk.com',1,1)

--提取文本中所有的HTML格式标签
SELECT dbo.RegexReplace('<(?:[^>''"]*|([''"]).*?\1)*>',
'','<a href="http://www.simple-talk.com">Simle Talk is wonderful</a><!--This is a comment --> we all love it',1,1)

--将已分隔的文本导入数据库，将其转换为插入语句
SELECT dbo.RegexReplace(
'([^\| ]+)[| ]+([^\| ]+)[| ]+([^\| ]+)[| ]+([^\| ]+)[| ]+',
'Insert into MyTable (Firstcol,SecondCol, ThirdCol, Fourthcol)
select $1,$2,$3,$4
','1|white gloves|2435|24565
2|Sports Shoes|285678|0987
3|Stumps|2845|987
4|bat|29862|4875',1,1)
/*

OLE正则式查找（执行）函数
-----------------------------

这是对于复杂的文本进行搜索替换操作时功能最强大的函数。它能返回成功匹配的细节，包括地址与回溯，便于复杂的操作。

 

该函数被设计成为函数。正则式子程序实际上返回了所有成功匹配的集合。在关系数据库中，您通常需要使用两个表格来表示它，因此我们使用了左外联接以便取回所有信息。这几乎可以满足我们所能想到的所有应用了。我们也追加了一个错误列，该列应是空列。

*/

IF OBJECT_ID(N'dbo.RegexFind') IS NOT NULL
DROP FUNCTION dbo.RegexFind
GO
CREATE FUNCTION RegexFind(
@pattern VARCHAR(255),
@matchstring VARCHAR(MAX),
@global BIT = 1,
@Multiline bit =1)
RETURNS
@result TABLE
(
Match_ID INT,
FirstIndex INT ,
length INT ,
Value VARCHAR(2000),
Submatch_ID INT,
SubmatchValue VARCHAR(2000),
Error VARCHAR(255)
)


AS -- 由函数返回的列（译注：此处列是复数，表示有可能不止一列）
BEGIN
DECLARE @objRegexExp INT,
@objErrorObject INT,
@objMatch INT,
@objSubMatches INT,
@strErrorMessage VARCHAR(255),
@error VARCHAR(255),
@Substituted VARCHAR(8000),
@hr INT,
@matchcount INT,
@SubmatchCount INT,
@ii INT,
@jj INT,
@FirstIndex INT,
@length INT,
@Value VARCHAR(2000),
@SubmatchValue VARCHAR(2000),
@objSubmatchValue INT,
@command VARCHAR(8000),
@Match_ID INT

DECLARE @match TABLE
(
Match_ID INT IDENTITY(1, 1)
NOT NULL,
FirstIndex INT NOT NULL,
length INT NOT NULL,
Value VARCHAR(2000)
)
DECLARE @Submatch TABLE
(
Submatch_ID INT IDENTITY(1, 1),
match_ID INT NOT NULL,
SubmatchNo INT NOT NULL,
SubmatchValue VARCHAR(2000)
)



SELECT @strErrorMessage = 'creating a regex object',@error=''
EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
IF @hr = 0
SELECT @strErrorMessage = 'Setting the Regex pattern',
@objErrorObject = @objRegexExp
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
IF @hr = 0
SELECT @strErrorMessage = 'Specifying a case-insensitive match'
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
IF @hr = 0
SELECT @strErrorMessage = 'Doing a match'
IF @hr = 0
EXEC @hr= sp_OAMethod @objRegexExp, 'execute', @objMatch OUT,
@matchstring
IF @hr = 0
SELECT @strErrorMessage = 'Getting the number of matches'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, 'count', @matchcount OUT
SELECT @ii = 0
WHILE @hr = 0
AND @ii <@Matchcount
BEGIN

/*Match对象具有四种只读属性。
FistIndex属性表示匹配中从左到右数，该字串所含字符的个数。
Match对象中的Length属性表示在该匹配中的字符数。
Value属性返回所匹配的文本。*/

SELECT @strErrorMessage = 'Getting the FirstIndex property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').FirstIndex'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command,
@Firstindex OUT
IF @hr = 0
SELECT @strErrorMessage = 'Getting the length property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').Length'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @Length OUT
IF @hr = 0
SELECT @strErrorMessage = 'Getting the value property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').Value'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @Value OUT
INSERT INTO @match
(
Firstindex,
[Length],
[Value]
)
SELLECT @firstindex + 1,
@Length,
@Value
SELECT @Match_ID = @@Identity

/*Match对象中的SubMatches属性是字串的组合。它仅当您的正则式中存在捕获组时才被赋值。该组合会分别对每组捕获保存一条字串。其Count属性（作为SubmatchCount返回）表示组合中返回中的字串数。其Item属性接收index参数，返回捕获组所匹配的文本。*/

IF @hr = 0
SELECT @strErrorMessage = 'Getting the SubMatches collection',
@command = 'item(' + CAST(@ii AS VARCHAR)
+ ').SubMatches'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command,
@objSubmatches OUT
IF @hr = 0
SELECT @strErrorMessage = 'Getting the number of submatches'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objSubmatches, 'count',
@submatchCount OUT
SELECT @jj = 0
WHILE @hr = 0
AND @jj &lt; @submatchCount
BEGIN
IF @hr = 0
SELECT @strErrorMessage = 'Getting the submatch value property',
@command = 'item(' + CAST(@jj AS VARCHAR)
+ ')' ,@submatchValue=NULL
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objSubmatches, @command,
@SubmatchValue OUT
INSERT INTO @Submatch
(
Match_ID,
SubmatchNo,
SubmatchValue
)
SELECT @Match_ID,
@jj+1,
@SubmatchValue
SELECT @jj = @jj + 1
END
SELECT @ii = @ii + 1
END
IF @hr &lt;&gt; 0
BEGIN
DECLARE @Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT

EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
@Descript