Chris Hedgate

While browsing the SqlServerCentral.com forums earlier today I saw a question that interested me. The person asking it wanted to split a string containing a repeating group of values into a set of values. I remembered that Eric Gunnerson is running a series of regular expression puzzle questions and that “one of the recent ones”:http://blogs.msdn.com/ericgu/archive/2006/01/16/513644.aspx was about just this, just not with the SQL Server variant. So, here is what we want to be able to do:

SELECT *
FROM dbo.SplitWithRangesExpanded( '1,3,7,12,15-20,23,27-29' );

The result should be this set { 1,3,7,12,15,16,17,18,19,20,23,27,28,29 }. The following code in C# can be used to define a streaming table-valued function in SQL Server.

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

public partial class Functions {
  [SqlFunction(DataAccess = DataAccessKind.None
    , SystemDataAccess = SystemDataAccessKind.None
    , IsDeterministic = true
    , IsPrecise = true
    , FillRowMethodName = "FillRow")]
  public static IEnumerable SplitWithRanges(SqlString input) {
    // First expand ranges
    Regex rangeExpander = new Regex(
      @"(?\d+)\-(?\d+)");

    string expandedRanges = rangeExpander.Replace(
      input.Value,
      new MatchEvaluator(Functions.ExpandRanges));

    // Now split the string containing the comma-separated values
    string[] splittedValues = expandedRanges.Split(
      new char[] { ',' },
      StringSplitOptions.None);

    return splittedValues;
  }

  private static String ExpandRanges(Match match) {
    int startOfRange = Int32.Parse(
      match.Groups["RangeStart"].Value);
    int endOfRange = Int32.Parse(
      match.Groups["RangeEnd"].Value);
    string[] values = new string[endOfRange - startOfRange + 1];
    for(int i = 0; i < values.Length; i++) {
      values[i] = (startOfRange + i).ToString();
    }
    return String.Join(",", values);
  }

  public static void FillRow(Object obj, out SqlString tuple) {
    tuple = new SqlString((String)obj);
  }
}

Note that the original code I wrote used an anonymous delegate for the regular expression match evaluator, but due to problems with shared state I had to explicitly define the function to use for it. Read the “companion post regarding these technical issues”:/articles/2006/10/04/troubles-with-shared-state-and-anonymous-delegates-in-sqlclr/ for more info.