CLR split function for comma separated values and return result in table format

create a new SQL type project named fn_split
assign a database where you want to use this CLR.
now create a class named fn_split
past the below code
after that build and deploy the code.
now you can use this function in sql
i.e. select * from fn_split('q,w,re,t,y',',')



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

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "CharlistFillRow", TableDefinition = "item nvarchar(4000)")]
public static IEnumerable fn_split(SqlString str, SqlString delimiter)
{
return str.Value.Split(new string[] { delimiter.Value.ToString() }, StringSplitOptions.None);
}
public static void CharlistFillRow(object row, out SqlString item)
{
item = new SqlString(row.ToString());
//str = str.Trim();
}
};

on sql server query prompt right this to create the assembly and function

CREATE ASSEMBLY fn_split FROM 'C:\somewhere\fn_split.dll'
go
CREATE FUNCTION fn_split (@list nvarchar(MAX), @delim nvarchar(50) = N',')
RETURNS TABLE (str nvarchar(4000))
AS EXTERNAL NAME fn_split.fn_split

Enable CLR using SQL SERVER

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO



check