SQL CLR .Net Function - Split

Having needed a split function many times in the past, I've always utilised patindex and done it the hard way…. And when it comes to nvarchar values, forget about it! So today I took a few minutes to write a SQL CLR function to do it.

The function takes a string and a delimiter and returns a table of string values.

Here are the instructions for those who may want to do the same thing…

1. Create a Visual Studio project of the type Database Projects \ SQL Server\ SQL-CLR \ C# SQL Server Project – I called it udfclrSplitString but you know, call it whatever you like!
   
2. Add the following code:
   

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

   public class CSRSplitString
   {
   [SqlFunction(FillRowMethodName = "FillRow")]
   

   public static IEnumerable udfclrSplitString(SqlString InputString,SqlString Delimiter)
   
   {
   
   return InputString.Value.Split(Delimiter.Value.ToCharArray(0, 1));
   }
   
   public static void FillRow(object row, out string InputString)
   {
   InputString = (string)row;
   InputString = InputString.Trim();
   }
   }
   

3. Build that sucker
   
4. Drop the dll onto the SQL server somewhere relevant
   
5. If CLR is not already enabled on the server, execute the TSQL code:
   

   sp_configure 'clr enabled', 1
   GO
   RECONFIGURE
   GO
   

6. In the desired database, execute the following TSQL code:
   

   CREATE ASSEMBLY CSRSplitString
   FROM 'D:\<YourDLLName>.dll'
   WITH PERMISSION_SET = SAFE
   

7. In the desired database, execute the following TSQL code:
   

   CREATE FUNCTION udfclrSplitString
   (@String nvarchar(4000),@Delimiter nvarchar(1))
   RETURNS
   TABLE (ArrayItem nvarchar(4000))
   AS EXTERNAL NAME CSRSplitString.CSRSplitString.udfclrSplitString;
   
   Where external name is MyDotNETAssembly.MyClassname.Method

8. To test it, try:
   

   select *
   from udfclrSplitString('Kristen,Patrick,Julian,Werner,Koe',',')
   
   You should get 5 rows back …

9. If you want to test it using a table column as the input try something like:
   
   Select a.name as DbNameOriginal,s.*
   from msdb.sys.databases a cross
   apply udfclrSplitString(a.name,'_') s
   where a.name like '%_%'
   
   You will need to have the function in the msdb database for this example to work
   
   
10. To make this function automatically available in all new databases created on that server, execute steps 6 + 7 in the model system database.