CLR split function return in two column Table format

This function return Two Column Table for comma separated values.

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',',')

output :
1 q
2 w
3 re....

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

public partial class UserDefinedFunctions
{
private class stringiter : IEnumerator
{
string _str;
char delim;
int _start_ix;
int _end_ix;
int _listpos;


public string str
{
get
{
return this._str;
}
}

public int start_ix
{
get
{
return this._start_ix;
}
}

public int end_ix
{
get
{
return this._end_ix;
}
}

public int listpos
{
get
{
return this._listpos;
}
}


public stringiter(SqlString str, SqlString delimiter)
{
this._str = str.IsNull ? "" : str.Value;
this.delim = delimiter.IsNull ? '\0': delimiter.Value.ToCharArray(0, 1)[0];
Reset();
}

public bool MoveNext()
{
this._start_ix = this._end_ix + 1;
if (delim == ' ')
{
while (this._start_ix < this._str.Length && this.str[this._start_ix] == ' ') { this._start_ix++; } } if (this._start_ix >= this._str.Length)
{
return false;
}

this._end_ix = this.str.IndexOf(this.delim, this._start_ix);
this._listpos++;
if (this.end_ix == -1)
{
this._end_ix = this._str.Length;
}
return true;
}

public Object Current
{
get
{
return this;
}
}

public void Reset()
{
this._start_ix = -1;
this._end_ix = -1;
this._listpos = 0;
}
}
[SqlFunction(FillRowMethodName = "CharlistFillRow",TableDefinition="idx int,item nvarchar(4000)")]
public static IEnumerator fn_split(SqlString str, SqlString delimiter)
{
return new stringiter(str, delimiter);
}

public static void CharlistFillRow(object obj,out int listpos,out string str)
{
stringiter iter = (stringiter)obj;
listpos = iter.listpos;
str = iter.str.Substring(iter.start_ix, iter.end_ix - iter.start_ix);
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

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

Asynchronous client script callbacks

the following link contain some useful information about Asynchronous client script callbacks.
Asynchronous client script callbacks

ASP.NET 4.0 Features

Please read the article in below link for ASP.net 4.0 features
ASP.net 4.0 features