CLR SQL SERVER: Let regular expressions also join your Transaction-sql

You've tried transaction-sql programming. This thing is disgusting, except the IDE, the most disgusting number of programming involved in the string concatenation problem. Imagine: In a huge complex business logic, it's full of while,if,case. You have to take care of all the situations and stitch strings in a certain rule. These strings may be SQL, or they may be the result, but whatever it is is our nightmare.

The regular expression is what to believe do not I introduced, processing text of the weapon AH. Although SQL Server also supports regular expressions, it can be handy to make a regular expression function by using more hassle. This section deals primarily with the CLR SQL Server technology, which was raised from SQL Server 2005 and is now a more mature technology. Now let's DIY our regular function!

Program code

/* Reference assembly */using System;  
Using System.Collections.Generic;  
Using System.Linq;  
      
      
Using System.Text;  
Using System.Data;  
Using System.Data.SqlClient;  
Using System.Data.SqlTypes;  
Using Microsoft.SqlServer.Server;  
Using System.Text.RegularExpressions;  
Using System.Xml.Linq;  
Using System.Xml;  
Using System.IO;  
      
      
Using System.Collections; <p> public class Clr_function {public clr_function () {}///Author: ghostbear/// Blog address: http://blog.csdn.net/ghostbear///table-valued function, a regular expression to split the object ().  
        The split result is output as a row record.  
        Example:///need to split the data: 1,2,3,4,5,12,8///to split the regular expression: \d{1,2}///Output results:///1  2///3///4///5///12///8/// </summary>///<param name= "input" > Data to be split </param>///<param name= "pattern" >  
   Regular expression to split </param>     <returns> Records after split </returns> [SqlFunction (tabledefinition= "Tmp_value nvarchar (max)", Fillrowmet Hodname= "Splictbyregex_fillrow")] public static IEnumerable splict_str_by_regex_1 (SqlString input, SqlString Pat  
            Tern) {ilist<string> result2 = new list<string> (); var matches = regex.matches (input. ToString (). Trim (), pattern. ToString ().  
                Trim ());</p><p> foreach (Match m in matches) {if (m.success) {result2.  
                ADD (M.value); }}</p><p> return RESULT2.  
        AsEnumerable ();  
            }</p><p> public static void Splictbyregex_fillrow (Object obj, out SqlString tmp) { TMP = new SqlString (obj.  
        ToString ()); }</p>}