regex split implementation for sql server

Question

hi

I have created a function to implement the regex.split in sql. Here's the code:

private static IEnumerable<IndexedValue<T>> ToIndexedValue<T>(IEnumerable<T> list)
{
  int idx = 1;
  foreach (T value in list)
    yield return new IndexedValue<T>(++idx, value);
}
private struct IndexedValue<T>
{
  public int Index;
  public T Value;
  public IndexedValue(int index, T value)
  {
    Index = index;
    Value = value;
  }
}
[SqlFunction(FillRowMethodName = "FillSplit",
  TableDefinition = "[ID] int, [Value] nvarchar(max)")]
public static IEnumerable RegexSplit(SqlString input, SqlString pattern)
{
  if (input.IsNull)
    input = String.Empty;
  if (pattern.IsNull)
    pattern = String.Empty;
  try
  {
    return ToIndexedValue<string>(Regex.Split(input.Value, pattern.Value, Options));
  }
  catch
  {
    throw;
  }
}
public static void FillSplit(object obj, out int id, out SqlString value)
{
  IndexedValue<string> iv = (IndexedValue<string>)obj;
  id = iv.Index;
  value = iv.Value;
}

However when i try it, i get id values but empty text values. Can someone help?

Answer 1

I suspect that the regular expression you're using is returning an array of empty strings or whitespace. Have you tried to set up a simple command line test frame for the expression itself?

I can't see anything wrong with this code except that Options is undefined. Is this a constant defined elsewhere?

Answer 2

Here you go (compile as SQL CLR assembly):

using System.Collections;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
  [SqlFunction]
  public static bool RegexMatch(string expr, string regex)
  {
    return Regex.IsMatch(expr, regex);
  }

  [SqlFunction]
  public static string RegexReplace(string expr, string regex, string replace)
  {
    return Regex.Replace(expr, regex, replace);
  }

  [SqlFunction(FillRowMethodName="GetToken", 
       TableDefinition="Value nvarchar(max)")]
  public static IEnumerable RegexSplit(string expr, string regex)
  {
    return Regex.Split(expr, regex);
  }

  public static void GetToken(object row, out string str)
  {
     str = (string) row;
  }
}

Originally from: http://stackoverflow.com/questions/2515259/microsoft-sql-equivalent-of-mysql-regexp