SQL CLR Function

One of the most commonly used functions in SQL is the ability to take a delimited string and use it in a query.  Before the advent of .NET the only real way to solve this problem was to break the string apart by brute force using SQL and looping through the string looking for the delimiter, tokenizing out the strings into a return table.

Those days are over.  Not only was that very cludgey but wasn't all that effecient as well.  Now with .NET we can create a CLR function to do all the heavy lifting.

First you will need to create a Database Project.  For this post I will be documenting how I did it using Visual Studio 2010.  First thing you will do is open up your IDE and navigate to "New Project --> Database --> SQL Server".  Take special note on how you name this project.  I would name it something like {dbName}CLR as it will be the project where you will put all of your CLR Functions for a specific database. 

Next you will then "Add a New Item" and choose the type "Class".  This class file can be named anything, but I would keep it sort of generic as it will most likely be the same project that you add all of your functions to.

Next cut and paste the following code below into your class file:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "fnToList", FillRowMethodName = "FillRow", TableDefinition = "ID NVARCHAR(255)")]
    public static IEnumerable SqlArray(SqlString str, SqlChars delimiter)
    {
        if (delimiter.Length == 0)
            return new string[1] { str.Value };
        return str.Value.Split(delimiter[0]);
    }

    public static void FillRow(object row, out SqlString str)
    {
        str = new SqlString((string)row);
    }
};

Before you can deploy the class, you will need to enable clr on your SQL Server.  To do this you can execute the following commands:

sp_configure 'clr enabled', 1;
reconfigure with override;

After you compile the Class, which will hopefully be error-free, you can then Deploy the project. When you deploy the project it will push the Assembly to the database that you specified during the Project creation. Visual Studio will also create the user defined function for you.
If you did everything correctly you should see in SSMS in your Table-valued Functions a new function.

To test this, you can simply run the following:
nd it should produce the following output: