SQL Server, RegEx, and User-Defined Functions

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
  Download C# Source Code
Here is a quick little code sample demonstrating how to utilize regular expressions from within SQL Server stored procedures.  It returns the matching keys back to the user defined function in the form of a TABLE that you can iterate through.  The technique described below applies to any situation where you'd like to return a resultset of records from a .NET method back into a stored procedure or user-defined function.
Here's what is covered in the sample:
 
1.Create the KeyMatch class an apply our regular expression processing.  In order for SQL Server to receive the results in the form of a TABLE, our KeyMatch class must inherit from IEnumerator and implement the following items enabling SQL Server to iterate over the results:  Current (returns the current item, MoveNext() (advances the index which Current depends on, and Reset() to rerun the process if needed.
2.Create the SqlRexHandler class to act as a seperate entity and interface to SQL Server.  This makes things a little cleaner for when we want to reuse our KeyMatch functionality in .NET applications but outside of SQL Server.
3.Register the .NET assembly in the desired database.
4.Create a user defined function that can utilize the .NET methods in SqlRegExHandler.
5.Show sample utilization of the user defined function.
 
Let's check out the code below:
 


Sample Console Application
using System;
using System.Diagnostics;
using System.Collections.Generic;
 
namespace NullSkull
{
 
   class EntryPoint
   {
 
     [STAThread]
     static void Main(string[] args)
     {
            
        // Let's extract any value that is
        // a number which starts with Q_";

        string pattern = "Q_[0-9]+";
        string text = "";
        NullSkull.KeyMatch parser = null;
 
        try
        {

           text = "((Q_1234/Q_5432)+(Q_12*Q_987))";

           parser = new NullSkull.KeyMatch(pattern,text);

           // We should see Q_1234, Q_5432, Q_12, and Q_987
           // as matches.

           while (parser.MoveNext())
           {
               Console.WriteLine(parser.Current.ToString());
           }
  
        }
        catch (Exception e) 
        {
           Debug.WriteLine(e.Message);
        }
        finally
        {
           Console.ReadLine(); 
        }

   }
	
  }
} 
 
Key Match
using System;
using System.Text;
using System.Text.RegularExpressions; 
using System.Collections;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Diagnostics;


namespace NullSkull
{
    public class KeyMatch : IEnumerator
    {

        // I chose an ArrayList for Items because it is serializable.
        // You could also use a DataSet here.

        private ArrayList Items = null;
        private int CurrentIndex = 0;
        private string Pattern = "";
        private string SearchString = "";

        public KeyMatch(string pattern, string searchString)
        {
            Items = new ArrayList();
            Pattern = pattern;
            SearchString = searchString;
            Reset();
        }

        // Return an instance of the current object
        // in the Items ArrayList based on the current index.

        public Object Current 
         {
             get
             {
                 if (CurrentIndex < 0)
                 {
                     CurrentIndex = 0;
                 }
                 return Items[CurrentIndex].ToString();  
             }
         }
        
        // Advance the interation

        public bool MoveNext()  
        {
             CurrentIndex += 1;

             if (CurrentIndex < Items.Count)
             {
                 return true;
             }

             return false;
         }

        public void Reset()   
        {

            Items = new ArrayList();
            CurrentIndex = -1;

            try
            {

                Regex r = new Regex(Pattern, RegexOptions.IgnoreCase);
                Match m = r.Match(SearchString);

               // Add successful matches to our Items
               // ArrayList.

                while (m.Success)
                {
                    Items.Add(m.Value);                  
                    m = m.NextMatch(); 
                }
               
            }
            catch (Exception) { throw; }
            return;
        }

  
    }
} 
 
Sql Handler
using System;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

namespace NullSkull
{
   
    public class SqlRegExHandler
    {

       // Tell SQL Server the name of the method it should
       // call in order to receive the values.  Also tell it
       // the schema of the in memory TABLE it will be throwing
       // the results into.

        [SqlFunction(FillRowMethodName = "FillRow",
              TableDefinition = "ItemsFound varchar(100)")]
        public static IEnumerator FindMatchingKeys(string pattern,
                                                   string searchString)
        {
          // Because KeyMatch inherits from IEnumerator, we
          // can just return the instance of the class.

            return new NullSkull.KeyMatch(pattern, searchString);
        }

        // The first parameter is required. The other parameters
        // need to be defined in order matching the TableDefinition
        // above.  This sample only has one column and it is a string.
        // So, we define the datatype as SqlString and then ensure
        // we either return a SqlString.Null or a valid string.

        public static void FillRow(Object currentObject, out SqlString matchValue)
        {
            if (currentObject != null)
            {
                matchValue = new SqlString((string)currentObject);
            }
            else
            {
                matchValue = SqlString.Null;
            }
        }
 
    }
}
 
Register Assembly In SQL Server 2005

 -- Prior to running this statement in SQL Server Query Analyzer,
 -- we would have copied our compiled .NET Assembly to the desired
 -- folder (not typically C:\TEMP).

 -- This statement registers the assembly in our database.
 -- The PERMISSION_SET argument is set to SAFE telling
 -- SQL Server that it is restricted and can't do
 -- anything harmful.  

 CREATE ASSEMBLY NullSkull
   FROM 'C:\TEMP\NullSkull.dll'
   WITH PERMISSION_SET = SAFE;

Create a User-Defined Function In SQL Server 2005

 -- After we've registered our assembly in the
 -- database, we can create a user-defined function
 -- that can call it.
 
 -- Assemblies compiled in Visual Studio .NET may need
 -- the Namespace referenced for itself and when referencing
 -- the class name (in this case SqlRegExHandler).

 -- In the returned TABLE, you can reference the column name
 -- ItemsFound.

 create function FindMatchingKeys
 (
   @pattern nvarchar(500),
   @searchString nvarchar(2000)
 )
 returns table
 (
   ItemsFound nvarchar(100)
 ) as
 external name NullSkull.[NullSkull.SqlRegExHandler].FindMatchingKeys

Sample Use
 
  -- Run this in query analyzer and should get two
  -- records returned:  Q_1234 and Q_5432

  declare @searchString nvarchar(2000)
  
  select @searchString = '(Q_1234 / Q_5432)'

  select ItemsFound
    from dbo.FindMatchingKeys('Q_[0-9]+',@searchString)


Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.