String.Find(..., pattern) in SQL server 2005 or above using CLR functions ...

SQL Server 2005 and above let developers take advantage of .NET through CLR assemblies and functions. In this post, we shall attempt to write a custom CLR function that allows us to search for a string pattern (a regular expression with named groups).

Example:

Input string - Kiran123Banda456Kiran789
If the requirement is to capture all those numbers that immediately occur after the word "Kiran", then the expected result is 123,789 (assuming that multiple occurances are concatenated with a comma).

The code for the custom CLR function is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;


[Serializable]
public class StringSearch
{
    /// 

    /// Finds the specified input string.
    /// 
    /// The input string.
    /// The search pattern.
    /// 
    [SqlFunction()]
    public static SqlString Find(SqlString inputString, SqlString searchPattern)
    {

        StringBuilder sb = new StringBuilder();

        Match match = Regex.Match(inputString.Value, searchPattern.Value, RegexOptions.Compiled);
        bool matchResult = match.Success;

        do
        {
            if (match.Groups.Count > 0 && match.Groups["grpVar"] != null)
            {
                for (int i = 0, l = match.Groups["grpVar"].Captures.Count; i < l; i++)
                {
                    string trailingDigit = match.Groups["grpVar"].Captures[i].Value;
                    sb.Append(trailingDigit);
                    sb.Append(";");
                }

            }

        } while ((match = match.NextMatch()) != null && match.Success);

        return new SqlString(sb.ToString());

    }
}

Steps to deploy and use the custom CLR function

Copy the attached dll to a location, say, E:\tmp and run the following sql statements to register the custom assembly and the user-defined function

CREATE ASSEMBLY CustomAssembly FROM 'E:\tmp\CustomAssembly.dll'
GO

CREATE FUNCTION fnSearchString(@input nvarchar(1000),@searchPattern nvarchar(100)) RETURNS nvarchar(max) AS
EXTERNAL NAME CustomAssembly.StringSearch.Find
GO

exec sp_configure 'clr enabled','1'
Reconfigure with override

Example Usage :

DECLARE @inputString NVARCHAR(MAX)
DECLARE @searchPattern NVARCHAR(255) 
SET @inputString = 'Kiran123Banda456Kiran789'


SET @searchPattern = 'Kiran(?\d+)'

SELECT [dbo].[fnSearchString](@inputString,@searchPattern)

Output :

123,789