Creating a CLR Table Valued Function

Question

Am trying to create a simple table valued CLR function, which takes a comma separated string as a parameter, splits it up and returns it as multiple rows

Following a few online tutorials I ended up at:

[SqlFunction(FillRowMethodName = "FillRow",TableDefinition="val nvarchar(1000)")]
public static IEnumerable SqlFunction1(SqlString val)
{
    string[] splitStr = val.Value.Split(',');
    return splitStr;
}
private static void FillRow(Object obj, out SqlString str)
{
    object[] row = (object[])obj;
    str = (string)row[0];
}

However, executing it using

select * from dbo.SqlFunction1('1,2,3,4,5')

Returns the following error

Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function : 
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Object[]'.
System.InvalidCastException: 
   at UserDefinedFunctions.FillRow(Object obj, SqlString& str)
.

Answer 1

I'm no C# expert, I'm a SQL dev, but this code has worked for me in the past. The method accepts a parameterised delimiter too. Sorry, I cannot directly answer your question. I can't even give you a source and credit the original author of the code - suffice to say it wasn't me.

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

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "StringParserCLR",
    FillRowMethodName = "FillRow",
    TableDefinition = "string nvarchar(500)")]

    public static IEnumerable StringParserCLR(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);
    }
};