Including a function to calculate the Levenshtein distance between two strings, gets values indicating whether the given input string matches the given regular expression, a regex replacement function, and more...

//-----------------------------------------------------------------------
// <copyright file="ClrFunctions.cs" company="Tasty Codes">
//     Copyright (c) 2010 Chad Burggraf.
// </copyright>
//-----------------------------------------------------------------------

namespace Tasty.SqlServer
{
    using System;
    using System.Collections;
    using System.Data.SqlTypes;
    using System.Diagnostics.CodeAnalysis;
    using System.Text.RegularExpressions;
    using Microsoft.SqlServer.Server;

    /// <summary>
    /// A collection of helper functions that can be imported to SQL Server.
    /// </summary>
    [SuppressMessage("Microsoft.Design", "CA1053:StaticHolderTypesShouldNotHaveConstructors", Justification = "Required class format for SQL Server CLR integration.")]
    public partial class ClrFunctions
    {
        /// <summary>
        /// Calculates the Levenshtein distance between two strings.
        /// </summary>
        /// <param name="first">The first string to compare.</param>
        /// <param name="second">The second string to compare.</param>
        /// <returns>The Levenshtein distance between the two strings.</returns>
        [SqlFunction(IsDeterministic = true, IsPrecise = false)]
        public static SqlDouble Levenshtein(string first, string second)
        {
            first = (first ?? string.Empty).Trim().ToUpperInvariant();
            second = (second ?? string.Empty).Trim().ToUpperInvariant();
            double result = 0;

            if (string.IsNullOrEmpty(first) && string.IsNullOrEmpty(second))
            {
                result = 100;
            }
            else if (!string.IsNullOrEmpty(first) && !string.IsNullOrEmpty(second))
            {
                int fl = first.Length, sl = second.Length;
                int[,] d = new int[fl + 1, sl + 1];

                for (int i = 0; i <= fl; i++)
                {
                    d[i, 0] = i;
                }

                for (int i = 0; i <= sl; i++)
                {
                    d[0, i] = i;
                }

                for (int i = 1; i <= fl; i++)
                {
                    for (int j = 1; j <= sl; j++)
                    {
                        int cost = first[i - 1] == second[j - 1] ? 0 : 1;
                        d[i, j] = Math.Min(Math.Min(d[i - 1, j] + 1, d[i, j - 1] + 1), d[i - 1, j - 1] + cost);
                    }
                }

                result = Math.Round((1.0 - ((double)d[fl, sl] / (double)Math.Max(fl, sl))) * 100.0, 2);
            }

            return result;
        }

        /// <summary>
        /// Gets a value indicating whether the given input string matches the given regular expression.
        /// </summary>
        /// <param name="input">The input to check against the regular expression.</param>
        /// <param name="pattern">The regular expression patter to check.</param>
        /// <param name="ignoreCase">A value indicating whether to ignore case.</param>
        /// <param name="multiline">A value indicating whether to treat the input as multiline.</param>
        /// <returns>True if the input string matches the regular expression, false otherwise.</returns>
        [SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlBoolean RegexIsMatch(string input, string pattern, bool ignoreCase, bool multiline)
        {
            input = input ?? String.Empty;
            pattern = pattern ?? String.Empty;

            return Regex.IsMatch(input, pattern, CreateRegexOptions(ignoreCase, multiline));
        }

        /// <summary>
        /// Replaces matches of the given regulare expression string on the given input string with the given replacement string.
        /// </summary>
        /// <param name="input">The input string to replace matches on.</param>
        /// <param name="pattern">The regular expression pattern identifying the replacements to make.</param>
        /// <param name="replacement">The replacement string to use.</param>
        /// <param name="ignoreCase">A value indicating whether to ignore case.</param>
        /// <param name="multiline">A value indicating whether to treat the input as multiline.</param>
        /// <returns>The result of the replacement operation.</returns>
        public static SqlString RegexReplace(string input, string pattern, string replacement, bool ignoreCase, bool multiline)
        {
            input = input ?? String.Empty;
            pattern = pattern ?? String.Empty;

            return Regex.Replace(input, pattern, replacement, CreateRegexOptions(ignoreCase, multiline));
        }

        /// <summary>
        /// Splits the given input string using the given regular expression.
        /// </summary>
        /// <param name="input">The input string to split.</param>
        /// <param name="pattern">The regular expression pattern to split the string on.</param>
        /// <param name="ignoreCase">A value indicating whether to ignore case.</param>
        /// <param name="multiline">A value indicating whether to treat the input as multiline.</param>
        /// <returns>The input string split using the given regular expression.</returns>
        [SqlFunction(IsDeterministic = true, IsPrecise = true, FillRowMethodName = "FillRegexSplitRow")]
        public static IEnumerable RegexSplit(string input, string pattern, bool ignoreCase, bool multiline)
        {
            input = input ?? String.Empty;
            pattern = pattern ?? String.Empty;

            return Regex.Split(input, pattern, CreateRegexOptions(ignoreCase, multiline));
        }

        /// <summary>
        /// Gets a value indicating whether any of the values in a string set are equal to any
        /// of the values in a second string set. The string sets are tokenized by the given separator,
        /// which will default to the newline (\n) character if null or empty.
        /// </summary>
        /// <param name="referenceSet">The reference string set to check.</param>
        /// <param name="askingSet">The asking string set to check.</param>
        /// <param name="separator">The separator to use when splitting the strings into sets.</param>
        /// <returns>True if any of the values are equal, false otherwise.</returns>
        [SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlBoolean StringSetContainsAny(string referenceSet, string askingSet, string separator)
        {
            referenceSet = (referenceSet ?? String.Empty).Trim();
            askingSet = (askingSet ?? String.Empty).Trim();
            separator = separator ?? String.Empty;

            if (String.IsNullOrEmpty(separator))
            {
                separator = "\n";
            }

            string[] separatros = new string[] { separator };
            bool contains = String.IsNullOrEmpty(referenceSet) && String.IsNullOrEmpty(askingSet);

            if (!contains)
            {
                string[] reference = referenceSet.Split(separatros, StringSplitOptions.RemoveEmptyEntries);
                string[] asking = askingSet.Split(separatros, StringSplitOptions.RemoveEmptyEntries);

                foreach (string r in reference)
                {
                    string rtr = r.Trim();

                    if (!String.IsNullOrEmpty(rtr))
                    {
                        foreach (string a in asking)
                        {
                            string atr = a.Trim();

                            if (!String.IsNullOrEmpty(atr) && rtr.Equals(atr, StringComparison.OrdinalIgnoreCase))
                            {
                                contains = true;
                                break;
                            }
                        }
                    }

                    if (contains)
                    {
                        break;
                    }
                }
            }

            return contains;
        }

        /// <summary>
        /// Creates a <see cref="RegexOptions"/> enumeration using the given indicators.
        /// </summary>
        /// <param name="ignoreCase">A value indicating whether to include <see cref="RegexOptions.IgnoreCase"/>.</param>
        /// <param name="multiline">A value indicating whether to include <see cref="RegexOptions.Multiline"/>.</param>
        /// <returns>The created <see cref="RegexOptions"/>.</returns>
        private static RegexOptions CreateRegexOptions(bool ignoreCase, bool multiline)
        {
            RegexOptions options = RegexOptions.None;

            if (ignoreCase)
            {
                options = options | RegexOptions.IgnoreCase;
            }

            if (multiline)
            {
                options = options | RegexOptions.Multiline;
            }

            return options;
        }

        /// <summary>
        /// Fills a row generated by <see cref="RegexSplit"/>.
        /// </summary>
        /// <param name="obj">The object containing values to be used when filling the row.</param>
        /// <param name="part">The first column value.</param>
        [SuppressMessage("Microsoft.Performance", "CA1811:AvoidUncalledPrivateCode", Justification = "This method is called internally by the runtime.")]
        private static void FillRegexSplitRow(object obj, out string part)
        {
            part = (string)((object[])obj)[0];
        }
    }
}