SQL Server 2005 正则表达式使模式匹配和数目提取变得重便于

目录

CLR
用户定义函数
模式匹配
多少提取
模式存储
匹配
每当配合项中进行数量提取
总结


尽管 T-SQL
对大部分数目处理而言最好强大,但它们对文本分析或操作所提供的支持却不行少。尝试以内置的字符串函数执行另外复杂的公文分析会造成难于调试和保障的大的函数和贮过程。有再次好的艺术吗?

 

骨子里,正则表达式提供了再快速且重新优良之缓解方案。它在比文本以便标识记录点的补益显而易见,但是她的用并不仅仅限于此。我们将介绍如何实施各种简单或让人咋舌的天职,这些任务在
SQL Server™ 2000 中给视为不切实际或无容许的,但如今是因为 SQL Server 2005
对托管公共语言运行库 (CLR) 的支撑而行。

正则表达式对 SQL 来说不要新物。Oracle 在 10g
中援引了坐的正则表达式,而且不少开源数据库解决方案也采用某种正则表达式库。实际上,正则表达式可在
SQL Server 的头版本被运用,但是经过的效率很没有。

如果采用 sp_OACreate 存储过程,则可采取其它实现正则表达式的 OLE
自动化对象,但你必须首先创建一个 COM 对象,至少调用 IDispatch
一涂鸦,然后销毁是目标。多数情下,这样做效率太没有而且导致了最好多性问题。唯一的预备方案是创办扩展的存储过程。然而,现在时有发生
SQLCLR,CLR 用户定义函数 (UDF),它同意你使用 Microsoft® .NET Framework
创建高效之还减少了失误可能性的函数集。

CLR 用户定义函数

CLR 用户定义函数只是以 .NET 程序集中定义的静态方法(Visual Basic
中的共享函数)。要动 SQLCLR 对象,您要用新的 CREATE ASSEMBLY
语词以 SQL Server
注册程序集,然后以先后集中创建指为那个实现的依次对象。对函数而言,CREATE
FUNCTION 语句都扩大为永葆创建 CLR 用户定义函数。为了简化操作,使用 SQL
Server Project 时,Visual Studio® 2005
将意味你处理所有注册过程。此类项目与多数 Visual Studio
项目不同,因为当您尝试调试(或启动而休调试)时,项目将为再次编译,生成的顺序集与中间定义的具有
SQLCLR 对象将随着安排及 SQL Server,然后报到 SQL Server。然后,IDE
将运行吧品种指定的测试脚本。可以当 SQL 脚本和你的 .NET
代码中安断点,这样可以简化调试过程。

补给加函数就像以新类添加到另外其它项目项目一样。仅拿一个新项添加到路还要以提示时选“用户定义函数”。新办法虽然叫填补加至含有有函数的组成部分类。新章程还将生出一个适用它的
SqlFunction 属性。Visual Studio 使用此属性来创造注册函数所急需的 SQL
语句。SqlFunction 中的 IsDeterministic、IsPrecise、DataAccess 和
SystemDataAccess 字段也由 SQL Server 用于各种用途。

SQL Server 1

模式匹配

确定字符串是否与模式匹配是本着正则表达式的不过简便易行用,如图 1
所示,而且便于操作。

第一,我利用“选项”字段来储存函数的正则表达式选项。在这个景下,我选了
RegexOptions.SingleLine 和
RegexOptions.IgnorePatternWhitespace。前者指定单行模式,而后者则于正则表达式消除保留的空格并且启用是因为磅符号标记的诠释。仔细考虑同剖析后,您或许想使采用的另一个抉择是
RegexOption.Compiled。如果以 Compiled
用于大气采取的表达式,只要选不是极致多,您见面发觉明显的性质改进。反复用的表达式应简明编译。然而,对于充分少用的正则表达式,则毫不用
Compiled,否则会追加启动资金及内存开销。同样,您可能想如果经过点名您是不是想要编译表达式的另参数来增进通用的
RegexMatch
函数;这样,您可以根据具体情况确定花费额外开销而带的性能改进是否值得。

点名了若下的 RegexOptions 后,我下 SqlChars 数据类型而未是 SqlString
来定义 RegexMatch 函数。SqlString 数据类型转换成 nvarchar(4,000),而
SqlChars 转换成 nvarchar(max)。新的极酷尺寸功能允许字符串扩展及超过 SQL
Server 2000 的 8,000 字节限制。在整篇文章中,我尽可能采取 nvarchar(max)
并且最要命程度地管灵活性。然而,如果有相关字符串包含的字符都少 4,000
只,使用 nvarchar(4,000)
则性能可抱明确改进。您应检查一下您的一定需求及其相应代码。

此方吃之多余代码非常简单。通过定义的挑和提供的模式创造 Regex
实例,然后 IsMatch
方法以被用于确定指定的输入是否以及模式匹配。现在,您得以一个简练的查询添加到测试脚本:

select dbo.RegexMatch( N'123-45-6789', N'^\d{3}-\d{2}-\d{4}$' )

 

此语句被的模式是为此来测试美国社会平安码的简便测试。在新查询中装置断点,然后开单步调试函数。此函数允许你进行过多两样之测试,但自身以为而介绍多数口非考虑到之有些情节。例如,在数据库被保持一致的命名约定好关键,而编制查询来证明所有的囤积过程是否可集团的指点规范却生窘迫。RegexMatch
函数使得这个起任务变得愈简约。例如,以下查询测试好履是桩任务:

select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = N'PROCEDURE'
and dbo.RegexMatch( ROUTINE_NAME,
N'^usp_(Insert|Update|Delete|Select)([A-Z][a-z]+)+$' ) = 0

 

斯询问测试每个存储过程是否因“usp_”开头,后以及“Insert”、“Update”、“Delete”或“Select”,然后跟至少一个实体名称。此外,它还证实实体中之每个词是否以生写字母开始。请以马上四行代码和下就用内置函数的超负荷简化的本子相较:

select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = N'PROCEDURE'
and ( LEN( ROUTINE_NAME ) < 11
or LEFT( ROUTINE_NAME, 4 ) <> N'usp_'
or SUBSTRING( ROUTINE_NAME, 5, 6 ) not in
( N'Insert', N'Update', N'Delete', N'Select' ) )

 

即便代码数量多了,但此询问实际上缺少几宗正则表达式版本中带有的效用。首先,它不分轻重缓急写以以询问中运用排序来实施测试会要该任规则而比照。其次,它没有对包含在经过名称中之实际上实体名称执行另外测试。第三,问题在查询中测试的季个字符串的长度都为六只字符,这样我得以经过自六独字符中提取一个子串来简化代码,然后因每个可承受的操作进行比较。由于具有操作名称的长都为六独字符,因此该问题并无特定于此示例,但要构想一个可以指定更扑朔迷离动词(例如“Get”、“List”或“Find”)的专业。RegexMatch
函数可以轻松处理这些动词,因为它恰好是列表中的别备选方案。

证明是正则表达式的大面积用法,可以证实从电话号码到邮政编码以及由定义帐号数字格式的旁内容。CHECK
约束非常适合执行是起操作,如以下所示表定义。

CREATE TABLE [Account]
(
[AccountNumber] nvarchar(20) CHECK (dbo.RegexMatch(
[AccountNumber], '^[A-Z]{3,5}\d{5}-\d{3}$' ) = 1),
[PhoneNumber] nchar(13) CHECK (dbo.RegexMatch(
[PhoneNumber], '^\(\d{3}\)\d{3}-\d{4}$' ) = 1),
[ZipCode] nvarchar(10) CHECK (dbo.RegexMatch(
[ZipCode], '^\d{5}(\-\d{4})?$' ) = 1)
)

 

AccountNumber
列是以满足以下标准的妄动约定来证实的,即为三暨五独字母开始,后与五个数字,然后是一个破折号,最后以是三只数字。电话号码和邮政编码都因标准的美国电话号与邮政编码格式进行认证。RegexMatch
函数为 SQL Server 提供了重重效益,而 .NET
中之正则表达式实现提供的效力虽然更多,正使您当脚内容中将看到的一致。

SQL Server 2

数提取

正则表达式的分组功能可用以从字符串中领到数据。我的 RegexGroup 函数为
T-SQL 提供了这个功能:

[SqlFunction]
public static SqlChars RegexGroup(
SqlChars input, SqlString pattern, SqlString name )
{
Regex regex = new Regex( pattern.Value, Options );
Match match = regex.Match( new string( input.Value ) );
return match.Success ?
new SqlChars( match.Groups[name.Value].Value ) : SqlChars.Null;
}

 

其一函数和 RegexMatch 函数一样可创造 Regex 对象。然而,Match
对象无须用于测试相当而是为以输入字符串中找到的首先单门当户对配项创建的。Match
对象用于检索指定的组。如果以输入被莫找到匹配项,则归空值。如果你喜欢用编号组而未命名组,则是函数仍然有效。仅拿整数值传递让
SQL 代码中之函数,它会隐式地转换为 nvarchar 并且返回相应的组。

汝可以在 SELECT 列表中应用 RegexGroup
函数来起外有数码有被提取一定的消息有。例如,如果您发出一个存储了
URL 的排列,您现在足轻松地解析这 URL
以确定各个部分。此询问利用分组来确定存储于 UrlTable 表的 Url
列中的每个不同的服务器。

select distinct dbo.RegexGroup( [Url],
N'https?://(?<server>([\w-]+\.)*[\w-]+)', N'server' )
from [UrlTable]

 

若还可以当计算列中应用是函数。下面的表定义将电子邮件地址分为邮箱和域。

CREATE TABLE [Email]
(
[Address] nvarchar(max),
[Mailbox] as dbo.RegexGroup( [Address],
N'(?<mailbox>[^@]*)@', N'mailbox' ),
[Domain] as dbo.RegexGroup( [Address], N'@(?<domain>.*)', N'domain' )

 

邮箱列将回到电子邮件地址的信箱或用户称。域列将返回电子邮件地址的地面。

SQL Server 3

模式存储

这些函数使用的备模式都只为字符串,这表示中任何一个都不过存储在数据库被之一个表中。多数仓储国际数据的数据库都发生一个意味着国家之表。通过将额外列添加到此说明,您可储存特定于国之印证模式。这样只是容适用于某地址行的羁绊根据该行对应之国度只要变化。

每当象征客户端存储数据的数据库中,通常都闹一个表示客户端的说明。此表可用于存储允许而讲述在数据库被存储原始客户端数据方式的分组模式,这样您就可以创建计算列以便从客户端数据遭到领取实际得之多寡。例如,如果你的每个客户端都产生唯一的帐号方案同时你就需要该帐号的特定段,您可以轻松创建一个领每个客户端音是有的表达式。

SQL Server 4

匹配

不用确定字符串是否以及模式匹配,它有时要取每个匹配项。以前,这好像提取需要游标循环访问字符串的各个片。该过程不仅速度迟滞,而且代码也困难理解以及护卫。正则表达式是履行此操作的更好点子。现在之题目是哪些当
SQL 构造中回到全部所急需的数量。表值函数可以解决这题目。

表值函数有点类似先前之函数,但在个别独点有所不同。首先,应用及方式的性质必须完全声明回的申结构。其次,涉及个别独主意。第一独主意返回可枚举对象要不是事实上的函数结果。第二单道传递可枚举对象为填充各行的字段。通过枚举器检索的每个值都许诺与结果集的一行对应。.NET
Framework 中的 ICollection 接口实现了
IEnumerable,这表示任何聚众都不过由于第一独办法返回。Regex 类包含 Match
方法,该方式返回您可是使用的 MatchCollection。MatchCollection
的题目在,必须以 Match 方法返回前处理整个字符串。SQL Server
包括借助让以需要有的处理过程的优化措施,因此自再愿编写好之枚举器(按需要返回各匹配项)而非是先返回整个集合。此决定实际在优化枚举器之前如何使函数和应怎样对函数进行大量测试。

图 2
中的代码表示枚举器。跟踪各个匹配在返的匹配集中的职位时,MatchNode
类在字符串中查封装各个匹配。MatchIterator
类是可枚举的,它还处理正则表达式处理过程。它采用初转变的主要字来创造于头版本的框架还便宜之枚举器。它用遵循需要返回在输入字符串中检测及之逐条匹配项。

图 3
中之代码定义了表值 CLR UDF。RegexMatches 方法返回一个新的
MatchIterator。RegexMatches 方法中之 SqlFunctionAttribute
还连某些其他属性。TableDefinition
属性被设置为函数的表定义。FillRowMethodName
被装也调用返回可枚举对象的每个迭代的艺术名称。在此景下,该措施为
FillMatchRow。

对 MatchIterator 的每个迭代,MatchNode 将给作为第一独参数传递到
FillMatchRow 方法。FillMatchRow
方法的别参数必须声明也出口参数而且要和第一只函数中定义之表定义匹配。FillMatchRow
函数仅使用 MatchNode 属性来填充字段数据。

终极,您可通过之函数从字符串轻松地领取多个数据有。为了证明对
RegexMatches
函数的以,让咱处理一个字符串以便利用此询问来规定里头蕴涵多少个不同之只是词:

declare @text nvarchar(max), @pattern nvarchar(max)
select
@text = N'Here are four words.',
@pattern = '\w+'
select count(distinct [Text])
from dbo.RegexMatches( @text, @pattern )

 

本条示例非常简单。不过她经过删除不同之主要字来显示应用此函数的某些可能性还要返回字符串的总字数。许多网站的文件输入限制似乎也擅自长度的字符串。通过以此类测试和新的
nvarchar(max)
表示拟相结合,它好限制输入字数。此类查询而用以满足各种分析处理要求,而
RegexMatches
函数还可用来实施常见的天职。遗憾的凡,此类查询还反映出对以正则表达式的过于热衷。此例中经过“\w+”表达式完成的拆分操作可以恰好通过
String.Split
方法轻松地做到,那样速度会再次快。正则表达式是一个格外强大的工具,但肯定要确保有充分理由应用它们。可能有用于特定情景的双重简约且性能再优质的工具。

本身时时翻看 MSDN®
论坛受到关于如何以同排列值传递至囤过程的题目。我表现了各种复杂的章程,它们将这类似列表解析为实在列表以确定系记录。RegexMatches
函数提供了双重精简的道。

declare @pattern nvarchar(max), @list nvarchar(max)
select @pattern = N'[^,]+', @list = N'2,4,6'
select d.* from [Data] d
inner join dbo.RegexMatches( @list, @pattern ) re
on d.[ID] = re.[Text]

 

此模式以及其他不包含逗号的字符组匹配。如果叫得一个名为也 Data 的表和一个叫做吧
ID 的平头排列,此询问将赶回列表中标识的每个记录。鉴于 SQL Server
中之隐式转换职能,这样会另行产生因此。同一查询还只是用来整数、日期/时间、GUID
或浮点数据类。处理同列值的任何措施要利用多只函数或存储过程才能够上这种活程度。此函数还而用于未因逗号分隔的列表。也可是处理为空格、分号、制表符、回车或任何其它可认识别字符分隔的列表。

SQL Server 5

于配合项中展开数据提取

接近于返回匹配配项,我们还得起每个匹配项中提数据。尝试采取 SQL
来拓展这种操作是蛮困难的。通常,这仿佛任务将当应用程序而非是数据库被落实,这样会时有发生问题,因为使用该数据库的每个应用程序都不能不贯彻所急需经过。在这个情况下,合理的方法是以囤过程中贯彻此作用。

以及 RegexMatches
贯彻平等,我爱好下于定义之可枚举对象来回到组信息。由于我们还须于每个匹配项中循环访问组,因此分组是唯一略微复杂的操作。在图 4
中,GroupNode 类与 MatchNode
类一样,除了它还连该所代表的组的名号。GroupIterator 类与 MatchIterator
类类似,除了它还连返回每个组的额外循环。由于负有可枚举对象,因此自定义表值函数的经过和定义
RegexMatches 函数的历程同样。

在图 5
中,RegexGroups 函数定义和 RegexMatches
函数定义一样,除了她还回去匹配配项中含有组称的别数据列。通过这个函数,我们现而每当字符串中找到多个门当户对配项,并且只是从每个匹配项中提取一定的信息有。

处理数据库时,以不同格式导入数据是周边的天职。以逗号分隔格式导入文本则又宽广。多数开发人员创建这样的许程序,它处理每执、提取数额,然后也各国尽执行存储过程。尽管该过程中,但我愿意推荐外一样栽缓解方案。如果您可以以不折不扣文件传递到囤过程还要让存储过程处理整个过程,情况会怎么样?通常这种想法被认为最好复杂而一筹莫展兑现,但是经过
RegexGroups
函数,您可行使单一查询实际履行是起插入。例如,考虑以下客户数据。

2309478,Janet Leverling,J
2039748,Nancy Davolio,N
0798124,Andrew Fuller,M
4027392,Robert King,L

 

卿需要从各行获得三码不同的消息:七位数之客户号、客户称与单个字符的客户类型。通过以下表达式,您得领取所有三起信息。

(?<CustomerNumber>\d{7}),(?<CustomerName>[^,]*),(?<CustomerType>[A-Z])\r?\n

 

您现在面临的题材是,RegexGroups 函数回的结果未可知一直动用。您可以动用
SQL Server 2005
中之枢轴功能一旦休是游标来循环访问结果。将装有的拜会结果一块放入存储过程,这样你尽管取得了整所欲内容。图 6
中的囤积过程接受包含最多 2GB Unicode
数据的盖逗号分隔的文件的全部文件。它处理整个文件,将文件中之各国一样实施作为行插入到
Customer
表中。任何被分隔的公文文件都得以一如既往的法处理。对模式稍作变更就足以添加转义序列以支撑字符串中的逗号。

只是,此过程也再印证履行同一任务有强方式,而且有时正则表达式并非总是最佳选择。在此例中,使用枢轴功能有效地收回
RegexGroups
所实施的备操作以便为超常规分组格式返回数据。还可以用还简约且还高速的
TVF 将数据直接插入表中,它仅仅念博诸一样履,根据逗号执行
String.Split,然后回来各国一行。

SQL Server 6

总结

尽管这些匹配函数功能很强劲,但其还不到家。还有众多规定实施匹配操作方便方法的恐怕选择项。如果你的数据库排序不分轻重缓急写,您或许希望函数也坐无区分轻重缓急写的方式实施匹配操作。可能会见要求显式捕获选项以减少一些结果集。多推行选允许而也一些任务创造更纯粹的模式。您还是可能希望创建用户定义的品类以便将相当的所急需选择传递到每个函数,这样以允许每个函数的履下同样组不同的挑项。

若还许诺了解处理公事时见面干本地化问题。例如,.NET Framework Regex
类比我之以身作则中之拉丁语 Regex
类识别更多字符,因此当支付使国际数据的数据库时,应多加注意。

当,如本文中频繁提及的那么,尽管正则表达式极其强大,但请确保您确实需要该意义。某些任务通过还基本的家伙集来执行会另行快还重新简单。

为了好起见,我提供的示范缺乏验证和错误处理,这些是其它生产体系遭到都承诺包括的。应说明函数的每个输入而应由乃的求来规定哪些响应
null 或空的字符串输入。无法解析模式或选择无效时,Regex
类可能会见引发那个。应妥善处理这些很。

将正则表达式与 SQL
结合起来可提供成千上万处理数据的可选方法。使用这些函数可以减掉用力量丰富到数据库所要的时空以及若系统重新易维护。任何数据库都得下正则表达式,我建议您对这是函数进行考查以便发现新的、甚至又具创造性的用途。

FROM MSDN:
http://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx?loc=zh

相关文章