Using CLR, here's a much simpler alternative that works in all cases, yet 40% faster than the accepted answer:
using System;
using System.Collections;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public class UDF
{
[SqlFunction(FillRowMethodName="FillRow")]
public static IEnumerable RegexSplit(SqlString s, SqlString delimiter)
{
return Regex.Split(s.Value, delimiter.Value);
}
public static void FillRow(object row, out SqlString str)
{
str = new SqlString((string) row);
}
}
Of course, it is still 8 times slower than PostgreSQL's regexp_split_to_table
.