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 用于各个用途。

图片 1

形式匹配

规定字符串是否和情势匹配是针对性正则表明式的突出简便以,如图 1
所示,而且便于操作。

先是,我以“选项”字段来储存函数的正则表达式选项。在这意况下,我采用了
RegexOptions.SingleLine 和
RegexOptions.IgnorePattern惠特(Whit)espace。前者指定单行形式,而后人则从正则表明式消除保留的空格并且启用是因为磅符号标记的阐明。仔细考虑同分析后,您可能想只要使用的旁一个挑选是
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
中的正则表达式实现提供的效应则还多,正而你在底下内容校官看到底同一。

图片 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' )

 

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

图片 3

格局存储

那一个函数使用的拥有格局都只有为字符串,这代表中任何一个还只是存储在数据库被的一个表中。多数囤国际数据的数据库都来一个象征国家的表。通过将额外列添加至这表明,您可储存特定于国家之阐明形式。这样可容适用于某地址行的约按照该行对应之国度只要生成。

以代表客户端存储数据的数据库被,平时就闹一个意味客户端的表。此表可用于存储允许而讲述在数据库中蕴藏原始客户端数据形式的分组情势,这样您便可制造统计列以便从客户端数据被取实际用之数码。例如,假若你的每个客户端都发生唯一的帐号方案以你就需要该帐号的特定段,您可轻松成立一个领到每个客户端音对有的表达式。

图片 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
或浮点数据列。处理同排值的其他方需要动用五只函数或存储过程才会达到这种活程度。此函数还而用以未以逗号分隔的列表。也只是处理为空格、分号、制表符、回车或此外其他可认识别字符分隔的列表。

图片 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
所行之保有操作以便为分外分组格式重临数据。还足以用还简短且还高速的
电视机F 将数据直接插入表中,它仅仅读博诸一样实施,依照逗号执行
String.Split,然后回到各国一行。

图片 6

总结

即便那些匹配函数效率很强大,但它还不完善。还有众多确定实施匹配操作相当方法的恐怕采取项。假诺您的数据库排序不区分轻重缓急写,您可能要函数也因非区分轻重缓急写的法门实施匹配操作。可能会晤要求显式捕获选项以减小一些结果集。多执行选允许而也一些任务创制更纯粹的情势。您如故可能要创制用户定义的体系以便将适当的所用选取传递到每个函数,这样用同意每个函数的行下同一组不同的取舍项。

君还应掌握处理公事时会师波及本地化问题。例如,.NET Framework Regex
类比我之演示中的拉丁语 Regex
类识别更多字符,因而在开应用国际数据的数据库时,应多加留心。

本来,如本文中一再提及的那么,尽管正则说明式极其强大,但要确保您确实用该意义。某些任务通过重新基本的家伙集来执行会重快还还简短。

为有利于起见,我提供的以身作则缺乏验证和错误处理,这一个是另生产系列中都许诺包括的。应证函数的每个输入而应由乃的要求来规定什么响应
null 或空的字符串输入。不可能解析形式要选拔无效时,Regex
类可能会师掀起那么些。应妥善处理那多少个很。

拿正则表明式与 SQL
结合起来足提供多拍卖数量的可选方法。使用这么些函数可以削减用功能丰盛到数据库所要的日子以及要系统又便于维护。任何数据库都得运用正则表达式,我提议您对这是函数举行试验以便发现新的、甚至还具备成立性的用处。

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

相关文章