SQLServer2005正则表达式使模式匹配和数据提取变得更容易.doc
8页
SQLServer2005正则表达式使模式匹配和数据提取变得更容易丼专flQ本文讨论：I使用正则表达式进彳亍高效的SQL査询ISQLServer2005对正则表达式的支持I从SQLServer使用.NETRegex类|在数据库中有效地使用止则表达式本文使用了以下技术：SQLServer2005,・NETFrameworkGetthesamplecodeforthisarticle・NEW:Explorethesamplecodeonline!•or•代码下载位用：Regex2007_02.exe(154KB)目录iCLR用戸定义函数i模式匹配|数据提取|模式存储|匹配|在匹配项中进行数据提取|总结尽管「SQL对多数数据处理而言极其强大，但它对文木分析或操作所提供的支持却很少尝试使用内世的字符串函数执行任何复杂的文木分析会导致难丁•调试和维护的庞大的函数和存储过程有更好的办法吗？实际上，正则表达式提供了更高效且更佳的解决方案它在比较文木以便标识记录方面的益处显而易见，但是它的用途并不仅限丁•此我们将介绍如何执行各种简单或令人惊异的任务，这些任务在SQLServer™2000中被视为不切实际或不可能的，但现在山丁SQLServer2005对托管公共语言运行库（CL旳的支持而可行。
正则表达式对SQL來说并非新事物Oracle在10g中引进了内置的正则表达式,而且许多开源数据库解决方案也使用某种正则表达式库实际上，正则表达式可在SQLServer的早期版本中使用，但是过程的效率很低如果使用sp_OACreate存储过程，则可以使用任何实现正则表达式的OLE「I动化对彖，但您必须首先创建一个COM对奴，至少调用IDispatch一次，然后销毁此对象多数情况下，这样做效率太低而且导致了太多性能问题唯一的备选方案是创建扩展的存储过程然而，现在有SQLCLR,CLR用门定义函数（UDF）,它允许您使用Microsoft®.NETFramework创建高效的且减少了出错可能性的函数集CLR用户定义函数CLR用戸定义函数只是在.NET程序集中定义的静态方法(VisualBasic中的共享函数)要使用SQLCLR对象.您必须使用新的CREATEASSEMBLY语句在SQLServer注册程序集，然后在程序集中创建指向其实现的％个对象对函数而言.CREATEFUNCTION语句已扩展为支持创建CLR用户定义函数为了简化操作，使用SQLServerProject时，VisualStudio®2005将代表您处理所有注册过程。
此类项H与多数VisualStudio项匸I不同，因为当您尝试调试(或启动而未调试)时，项目将被重新编译，生成的程序集以及其中定义的所有SQLCLR对象将随后部署到SQLServer,然后注册到SQLServerQ然后，IDE将运行为项忖指定的测试脚木可以在SQL脚本和您的.NET代码中设连断点，这样可以简化调试过程添加函数就像将新类添加到任何其他项匸I类型一样仅将一个新项添加到项11并且在提示时选择“用户定义函数”o新方法则被添加到包含所有函数的局部类新方法还将有一个适用它的SqlFunction加性VisualStudio使用此属性來创建注册函数所需的SQL语句SqIFunction中的IsDeterministic、IsPrecise、DataAccess和SystemDataAccess字段也虫SQLServer用丁•各种用途模式匹配确定字符串是否与模式匹配是对正则表达式的最简单应用，如图1所示，而且易丁•操作//Figure1字符阳匹配publicstaticpartialclassUserDefinedFunctions{public3taticreadonlyRegexOptionsOptions=RegexOptions.IgnorePatternWhitespaceRegexOptions.Singleline:[SqlFunction]public3taticSqlBooleanRege>3!atch(SqlCharsinput,SqlStringpattern)(Regexregex=newRegex(pattern.Value,Options):returnregex.IsMatch(newstring(input.Value)):})首先.我使用“选项”字段來存储函数的疋则表达式选项。
在此情况下，我选择了RegexOptions.SingleLine和RegexOptions.IgnorePatternWhitespaceoijij者指定单行模式.而后者则从正则表达式消除保留的空格并且启用山磅符号标记的注释仔细考虑和分析后，您可能想要使用的另一个选项是RegexOption.Compiledc如果将Compiled用丁•大虽使用的表达式，只要选项不是太多，您会发现显著的性能改进反复使用的表达式应明确编译然而，对丁•很少使用的正则表达式，则不要使用Compiled,否则会增加启动成本和内存开销同样，您可能想要通过指定您是否想要编译表达式的其他参数來增强通用的RegexMatch函数：这样.您可以根据具体情况确定花费额外开销而带來的性能改进是否值得指定了要使用的RegexOptions后，我使用SqlChars数据类型而不是SqlString來定义RegexMatch函数SqlString数据类型转换成nvarchar(4,000),而SqlChars转换成nvarchar(max)o新的最大尺寸功能允许字符串扩展到超过SQLServer2000的8,000字节限制在整篇文章中,我尽可能使用nvarchar(max)并且最大程度地保证灵活性。
然而，如果所有相关字符串包含的字符都少T4,000个.使用nvarchar(4,000)则性能可得到显著改善您应检查一下您的特定需求及其相应代码此方法中的余下代码非常简单通过定义的选项和提供的模式创建Regex实例.然后IsMatch方法将被用丁•确定指定的输入是否与模式匹配现在，您需要将一个简单的查询添加到测试脚本：selectdbo.RegexMatch(N*123-15-6789*,A\d{3}-\d{2}-\d{4))此语句中的模式是用來测试美国社会安全号码的简单测试在新査询中设代断点.然后开始单步调试函数此函数允许您进行许多不同的测试，但我将为您介绍多数人耒考虑到的一些内容例如，在数据库中保持一致的命名约定非常重要，而编写查询來验证所有的存储过程是否符合组织的指导療则却很困难RegexMatch函数使得此项任务变得更加简单例如，以下査询测试可以执行此项任务：selectROUTINE.NAMEfromINFORMATION^SCHEMA.ROUTINESwhereROUTINE.TYPE=N'PROCEDURE*anddbo.RegexMatch(R0UTINE.NAME,N*"usp_(Insert|UpdateDeleteSelect)([A~2])=0此査询测试毎个存储过程是否以"usp_”开头，后跟"Insert"、“Update”、“Delete"或“Select”，然后跟至少个实体名称。
此外，它还验证实体中的每个词是否以大写字母开始请将这四行代码与下面仅使用内置函数的过丁•简化的版本相比较：selectROUTINE.NAMEfromINFORMATION^SCHEMA.ROUTINESwhereROUTINE_TYPE=N'PROCEDURE*and(LEX?ROUTINE.NAME)<11orLEFT(ROUTINE.NAME,4)<>N'orSUBSTRING(ROVTINE.NAME,5,6)notin(N*Insert*,JTUpdate',X*Delete',N*Select*))即使代码数戢多了，但此査询实际上缺少几项正则表达式版木中包伤的功能首先，它不区分大小写而口在査询中使用排序來执彳j•测试会使其无规则可循其次，它并耒对包含在过程名称中的实际实体名称执行任何测试第三.问题在丁•查询中测试的四个字符串的长度均为六个字符，这样我可以通过从六个字符中提取一个子串來简化代码，然后根据毎个可接受的操作进行比较山丁所有操作名称的长度均为六个字符，仄1此该问题并不特定丁•此示例.但需要构想一个可以指定更复杂动词（例如“Get”.'list”或"Find"）的标准。
RegexMatch函数可以轻松处理这些动词，因为它们恰好是列表中的其他备选方案验证是正则表达式的常见用法.可以验证从号码到邮政编码以及“定义帐号数字格式的任何内容CHECK约束非常适合执行此项操作，如以下所示表定义CREATETABLE[Account]([AccountNumbeir]nvarchar(20)CHECK(dbo.RegexMatch([AccountNumber],[A-Z]{3,5?\d{5}-\d{3}S*)=1),[PhoneNumber]nchar(13)CHECK(dbo.RegexMatch(rPhoneNumber],f*\(\d{3}\)\d{3}-\d{4}S*)=1),[ZipCodelnvarchar(10)CHECK(dbo.RegexMatch([ZipCode],,A\d{5}(\-\d{4})?$,)=1))AccountNumber列是按照满足以下条件的任意约定來验证的，即以三到五个字母开始，后跟五个数字，然后是一个破折号，最后又是三个数字号码和邮政编码都根据标准的美国号码和邮政编码格式进行验证RegexMatch函数为SQLServer提供了许多功能.而・NET中的正则表达式实现提供的功能则更多，正如您在下而内容中将看到的一样。
数据提取疋则表达式的分组功能可用丁从字符串中提収数据我的RegexGroup函数为T-SQL提供了此功能:[SqlFunctionIpublicstaticSqlCharsRegexGroup(SqlCharsinput,SqlStringpattern,SqlStringname){Regexregex=newRegex(pattern.Value,Options):Matchmatch=regex.Match(newstring(input.Value)):returnmatch.Success?newSqlChars(match.Groups[name.Value].Value):SqlChars.Null;)此函数同RegexMatch函数一样可创建Regex对象然而，Match对彖并非用丁•测试匹配而是为在输入字符串中找到的第一个匹配项创建的Match对彖用丁•检索指定的组如果在输入中耒找到匹配项，则返也1空值如果您喜欢用编号组而非命名组，则此函数仍然有效仅将整数值传递给SQL代码中的函数.它会隐式地转换为nvarchar并且返回相应的组您可以在SELECT列表中使用RegexGroup函数來从其他一些数据片段中提取特定的信息片段。
例如，如果您有一个存储了URL的列.您现在可以轻松地分析此URL以确定各个片段此查询使用分组來确定存储在UrlTable表的Url列中的毎个不同的服务器selectdistinctdbo.RegexGroup([Url],N'https?://(?
