Wednesday, 10 October 2007

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.

4 comments:

Boot2TheHead said...

Wow that's some tortured Hungarian notation there. Also, did you do any performance testing against the conventional version of this same proc? This is one that people usually have laying around already so a CLR proc would be reinventing the wheel wouldn't it?

Kristen Hodges said...

When you say conventional I'm presuming you mean using a stored proc? Which won't help if you want to split an nvarchar.

Nathon said...

I get an error when compiling in VS.NET saying...

The method "SplitString" in class "UserDefinedFunctions" marked as a table-valued function must define a table definition in the SqlFunction attribute.

A search has turned up this site saying that it requires defining the table in the SqlFunction attribute like this...

[SqlFunction(FillRowMethodName = "FillRow", TableDefinition = "Value nvarchar(4000))]

Anonymous said...

Perfect...many thanks