Run the following code to create the fn_SplitTSQL inline table-valued function:

USE CLRUtilities;
GO
IF OBJECT_ID('dbo.fn_SplitTSQL') IS NOT NULL
  DROP FUNCTION dbo.fn_SplitTSQL;
GO
CREATE FUNCTION dbo.fn_SplitTSQL
  (@string NVARCHAR(MAX), @separator NCHAR(1) = N',') RETURNS TABLE
AS
RETURN
  SELECT
    n - LEN(REPLACE(LEFT(s, n), @separator, '')) + 1 AS pos,
    SUBSTRING(s, n,
      CHARINDEX(@separator, s + @separator, n) - n) AS element
  FROM (SELECT @string AS s) AS D
    JOIN dbo.Nums
      ON n <= LEN(s)
      AND SUBSTRING(@separator + s, n, 1) = @separator;
GO

The function accepts two input parameters: @string and @separator. The @string parameter holds the input array, and @separator holds the character used to separate the elements in the array. The function queries a derived table called D that has a single row and a single column called array, which represents the input array. The function joins D with the Nums auxiliary table to generate as many copies of array as the number of elements. The join finds a match for each @separator value that appears in @separator + array. In other words, array will be duplicated once for each element, and n from Nums will represent the starting position of an element.

The SELECT list has an expression invoking the SUBSTRING function to extract the element starting at the nth character up until the next occurrence of @separator in array. The SELECT list has another expression that uses the technique I described earlier in the chapter to count occurrences of a substring within a string. In our case, the technique is used to count the number of occurrences of @separator in the first n characters within array. This count plus one is in fact the position of the current element within array.

To test the fn_SplitTSQL function, run the following code, which generates the output shown in Table 6-7:

SELECT pos, element FROM dbo.fn_SplitTSQL(N'a,b,c', N',') AS F;

You can use the function in interesting ways. For example, suppose that a client application needs to send SQL Server a comma-separated list of order IDs and expects to get back information about orders whose keys appear in the list. Typically, programmers implement such logic in a stored procedure using dynamic execution. I discussed both security and performance downsides of such an approach in Chapter 4. With your new function, you can answer such a need with a static query that will be able to reuse a previously cached execution plan:

DECLARE @arr AS NVARCHAR(MAX);
SET @arr = N'10248,10249,10250';

SELECT O.OrderID, O.CustomerID, O.EmployeeID, O.OrderDate
FROM dbo.fn_SplitTSQL(@arr, N',') AS F
  JOIN Northwind.dbo.Orders AS O
    ON CAST(F.element AS INT) = O.OrderID;

This query generates the output shown in Table 6-8.

The CLR implementation of the split function is simpler, although it actually uses two methods. Here’s the C# definition of the fn_SplitCLR function:

// Struct used in string split functions
struct row_item
{
    public string item;
    public int pos;
}

// Split array of strings and return a table
// FillRowMethodName = "ArrSplitFillRow"
[SqlFunction(FillRowMethodName = "ArrSplitFillRow",
 DataAccess = DataAccessKind.None,
 TableDefinition = "pos INT, element NVARCHAR(4000) ")]
public static IEnumerable fn_SplitCLR(SqlString inpStr,
    SqlString charSeparator)
{
    string locStr;
    string[] splitStr;
    char[] locSeparator = new char[1];
    locSeparator[0] = (char)charSeparator.Value[0];
    if (inpStr.IsNull)
        locStr = "";
    else
        locStr = inpStr.Value;
    splitStr = locStr.Split(locSeparator,
        StringSplitOptions.RemoveEmptyEntries);
    //locStr.Split(charSeparator.ToString()[0]);
    List<row_item> SplitString = new List<row_item>();
    int i = 1;
    foreach (string s in splitStr)
    {
        row_item r = new row_item();
        r.item = s;
        r.pos = i;
        SplitString.Add(r);
        ++i;
    }
    return SplitString;
}

public static void ArrSplitFillRow(
  Object obj, out int pos, out string item)
{
    pos = ((row_item)obj).pos;
    item = ((row_item)obj).item;
}

The function’s header sets the FillRowMethodName attribute to ″ArrSplitFillRow″. ArrSplitFillRow is a method (defined after the fn_SplitCLR function’s definition) that simply converts the input object to a string. The header also defines the schema of the output table in the TableDefinition attribute. This attribute is needed only if you deploy the function automatically using Visual Studio. If you deploy the function manually using T-SQL, you don’t need to specify this attribute.

The function simply invokes the built-in Split method of the string type to split the input array (after converting the input array from a .NET SQL type SqlString to a .NET native type string). It uses the StringSplitOptions.RemoveEmptyEntries Split method option, so the return value does not include array elements that contain an empty string.

Here’s the Visual Basic version of the fn_SplitCLR function:

'Struct used in string split functions
Structure row_item
    Dim item As String
    Dim pos As Integer
End Structure

' Split array of strings and return a table
' FillRowMethodName = "ArrSplitFillRow"
<SqlFunction(FillRowMethodName:="ArrSplitFillRow", _
   DataAccess:=DataAccessKind.None, _
   TableDefinition:="pos INT, element NVARCHAR(4000) ")> _
Public Shared Function fn_SplitCLR(ByVal inpStr As SqlString, _
  ByVal charSeparator As SqlString) As IEnumerable
    Dim locStr As String
    Dim splitStr() As String
    Dim locSeparator(0) As Char
    locSeparator(0) = CChar(charSeparator.Value(0))
    If (inpStr.IsNull) Then
        locStr = ""
    Else
        locStr = inpStr.Value
    End If
    splitStr = locStr.Split(locSeparator, _
      StringSplitOptions.RemoveEmptyEntries)
    Dim SplitString As New List(Of row_item)
    Dim i As Integer = 1
    For Each s As String In splitStr
        Dim r As New row_item
        r.item = s
        r.pos = i
        SplitString.Add(r)
        i = i + 1
    Next
    Return SplitString
End Function

Public Shared Sub ArrSplitFillRow( _
ByVal obj As Object, <Out()> ByRef pos As Integer, _
  <Out()> ByRef item As String)
    pos = CType(obj, row_item).pos
    item = CType(obj, row_item).item
End Sub

Use the following code to register the C# version of the function in the database:

IF OBJECT_ID('dbo.fn_SplitCLR') IS NOT NULL
  DROP FUNCTION dbo.fn_SplitCLR;
GO
CREATE FUNCTION dbo.fn_SplitCLR
  (@string AS NVARCHAR(4000), @separator AS NCHAR(1))
RETURNS TABLE(pos INT, element NVARCHAR(4000))
EXTERNAL NAME CLRUtilities.CLRUtilities.fn_SplitCLR;

Use the following code to register the Visual Basic version:

CREATE FUNCTION dbo.fn_SplitCLR
  (@string AS NVARCHAR(4000), @separator AS NCHAR(1))
RETURNS TABLE(pos INT, element NVARCHAR(4000))
EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_SplitCLR;

Run the following query to test the fn_SplitCLR function, and produce the output shown earlier in Table 6-7:

SELECT pos, element FROM dbo.fn_SplitCLR(N'a,b,c', N','),

To test the function against a table of arrays, first run the following code, which creates the Arrays table and populates it with some sample arrays:

IF OBJECT_ID('dbo.Arrays') IS NOT NULL
  DROP TABLE dbo.Arrays;
GO
CREATE TABLE dbo.Arrays
(
  arrid INT            NOT NULL IDENTITY PRIMARY KEY,
  arr   NVARCHAR(4000) NOT NULL
);

INSERT INTO dbo.Arrays(arr) VALUES(N'20,220,25,2115,14'),
INSERT INTO dbo.Arrays(arr) VALUES(N'30,330,28'),
INSERT INTO dbo.Arrays(arr) VALUES(N'12,10,8,8,122,13,2,14,10,9'),
INSERT INTO dbo.Arrays(arr) VALUES(N'-4,-6,1050,-2'),

Use the following query to apply the function to each array from the Arrays table, and generate the output shown in Table 6-9:

SELECT arrid, pos, element
FROM dbo.Arrays AS A
  CROSS APPLY dbo.fn_SplitCLR(arr, N',') AS F;

To compare the performance between the T-SQL and CLR splitting techniques, first duplicate the current contents of Arrays 100,000 times by running the following code:

INSERT INTO dbo.Arrays
  SELECT arr
  FROM dbo.Arrays, dbo.Nums
  WHERE n <= 100000;

The Arrays table is now populated with 400,004 rows.

Use the following query (with results discarded) to apply the T-SQL splitting technique:

SELECT
  n - LEN(REPLACE(LEFT(arr, n), ',', '')) + 1 AS pos,
  SUBSTRING(arr, n, CHARINDEX(',', arr + ',', n) - n) AS element
FROM Arrays
  JOIN dbo.Nums
    ON n <= LEN(arr)
    AND SUBSTRING(',' + arr, n, 1) = ',';

Notice that I didn’t use the fn_SplitTSQL UDF here because you can use the same technique directly against the Arrays table. This code ran for 17 seconds on my system.

As for the CLR version, it ran for 8 seconds–twice faster than the T-SQL version.

When you’re done, run the following code for cleanup:

IF OBJECT_ID('dbo.Arrays') IS NOT NULL
  DROP TABLE dbo.Arrays;
GO
IF OBJECT_ID('dbo.fn_SplitTSQL') IS NOT NULL
  DROP FUNCTION dbo.fn_SplitTSQL;
GO
IF OBJECT_ID('dbo.fn_SplitCLR') IS NOT NULL
  DROP FUNCTION dbo.fn_SplitCLR;