让正则表达式也加入你的Transaction-SQL吧！（CLR SQL SERVER）

 试过Transaction-Sql编程的哥们应该都觉的这东西太恶心了，除了IDE，最恶心得还数编程中涉及的字符串拼接问题。想象一下：在一个巨复杂的业务逻辑中，里面充满了while，if，case。你必须处理好所有的情况并按某一规则来拼接字符串。这些字符串可能是Sql，也可能是结果，但不管是什么都是我们的噩梦。

   正则表达式是啥相信就不要我介绍了，处理文本的利器呀。虽然Sql Server也支持正则表达式，但使用比较麻烦，还是自己制作一个正则表达函数来的方便。这节主要涉及了CLR Sql Server技术，该技术是从Sql Server 2005时被提出来的，就目前来看是个比较成熟的技术。现在我们就来DIY自己的正则函数吧！

程序代码

 

[csharp] view plain copy

print?

1. /*引用程序集*/  
2. using System;  
3. using System.Collections.Generic;  
4. using System.Linq;  
5. using System.Text;  
6.   
7.   
8. using System.Data;  
9. using System.Data.SqlClient;  
10. using System.Data.SqlTypes;  
11. using Microsoft.SqlServer.Server;  
12. using System.Text.RegularExpressions;  
13. using System.Xml.Linq;  
14. using System.Xml;  
15. using System.IO;  
16. using System.Collections;  
17.   
18.   
19. <p> public class CLR_FUNCTION  
20.     {  
21.         public CLR_FUNCTION() { }   
22.         /// 作者：GhostBear  
23.         /// 博客地址： http://blog.csdn<a href="http://lib.csdn.net/base/dotnet" class="replace_word" title=".NET知识库" target="_blank" style="color:#df3434; font-weight:bold;">.NET</a>/ghostbear  
24.         /// 表值函数，通过正则表达式来将对象进行拆分()。拆分结果以行记录的形式输出。  
25.         /// 例：  
26.         ///   需要拆分的数据：1,2,3,4,5,12,8  
27.         ///   进行拆分的正则表达式：\d{1,2}  
28.         ///   输出的结果：  
29.         ///   1  
30.         ///   2  
31.         ///   3  
32.         ///   4  
33.         ///   5  
34.         ///   12  
35.         ///   8  
36.         /// </summary>  
37.         /// <param name="input">需要拆分的数据</param>  
38.         /// <param name="pattern">用来拆分的正则表达式</param>  
39.         /// <returns>拆分后的记录</returns>  
40.         [SqlFunction(TableDefinition="tmp_value nvarchar(max)",FillRowMethodName="SplictByRegex_FillRow")]  
41.         public static IEnumerable SPLICT_STR_BY_REGEX_1(SqlString input, SqlString pattern)  
42.         {  
43.             IList<string> result2 = new List<string>();  
44.             var matches = Regex.Matches(input.ToString().Trim(), pattern.ToString().Trim());</p><p>            foreach (Match m in matches)  
45.             {  
46.                 if (m.Success)  
47.                 {  
48.                     result2.Add(m.Value);  
49.                 }  
50.             }</p><p>            return result2.AsEnumerable();  
51.         }</p><p>        public static void SplictByRegex_FillRow(object obj, out SqlString tmp)  
52.         {  
53.             tmp = new SqlString(obj.ToString());  
54.         }</p>  
55.   
56. }  

代码分析

    我们定义的这两个C#函数会映射为数据库的表值函数，该函数的具体思路是：通过正则表达式将一段字符内容进行分拆，分拆的过程有点象C#中我们用到的Split函数，但我们这里用的是正则来进行分拆，所以比它要强大。为了方便读取分拆后的结果，就将分拆的结果组织成结果集的形式进行返回。

 

 

部署步骤

 

[sql] view plain copy

print?

1. --在Sql Server中开启对程序集的信任  
2. sp_configure [clr enabled],1 reconfigure  
3.   
4.   
5. --注册程序集  
6. create assembly Liby_SQL_CLR_FUNCTION from  
7. 'C:\CLR_FUNCTION\编译后生成的名称.dll'  
8.   
9. --注册函数  
10. create function SPLICT_STR_BY_REGEX_1(@input nvarchar(max),@pattern nvarchar(max)) returns table(tmp_value nvarchar(max))  
11. as  
12. external name 程序集名称.CLR_FUNCTION.[SPLICT_STR_BY_REGEX_1]  

 

执行结果

 

[sql] view plain copy

print?

1. select * from SPLICT_STR_BY_REGEX_1('1,2,3,4,5,12','\d{1,2}')  

 

小结

        编程最重要的就是效率，详细把正则引进到数据库编程中会轻松的解决很多难题，希望这篇博文能对大家有所帮助。

转载于:https://my.oschina.net/u/3270404/blog/868849