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
