SQL Server String Split T-SQL CLR Function Sample
String split is a major problem and a vital requirement in every programming language as well as in SQL Server transact-sql.
There is not a built in SQL Split function that comes out-of-box with MS SQL Server even with SQL Server 2005 and SQL Server 2008.
But T-SQL programmers have developed many sql split string functions using t-sql codes.
In this web site you can find a few of those mentioned sql server split string functions.
But since string split process is not a major task of database applications, databases like Microsoft SQL Server are not expected to give high performance on records of thousands and millions when sql split string is the topic.
SQL Server CLR It is one of the best enhancements of Microsoft SQL Server 2005 on t-sql development.
SQL CLR enables developers to create sql clr stored procedure and sql server clr function to build complex processes easier and perform better using VB.NET or C#.
String split is one of the sample applications where SQL Server CLR functions will perform better in SQL Server 2005 and SQL Server 2008.
In this sql clr tutorial, we will see VB.NET codes of a SQL Server CLR function.
And later we will create sql split function referring that sql CLR string split function.
CLR Split String Function VB.NET Code
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Public Class SQLCLRFunctions
<SqlFunction(FillRowMethodName:="FillRowMethod", TableDefinition:="String nvarchar(max)")> _
Public Shared Function SplitString(ByVal InputString As SqlString, ByVal Seperator As SqlString, ByVal RemoveEmpty As Boolean) As IEnumerable
Dim DelimitersList() As String
ReDim DelimitersList(0)
DelimitersList(0) = Seperator.Value.ToString
If RemoveEmpty Then
Return InputString.Value.Split(DelimitersList, System.StringSplitOptions.RemoveEmptyEntries)
Else
Return InputString.Value.Split(DelimitersList, StringSplitOptions.None)
End If
End Function
Public Shared Sub FillRowMethod(ByVal row As Object, ByRef InputString As String)
InputString = DirectCast(row, String)
InputString = InputString.Trim()
End Sub
End Class
Build CLR project using Ctrl+Shift+B
------ Build started: Project: CLRSplit, Configuration: Debug Any CPU ------
CLRSplit -> C:\VS\2008\CLR-Split\CLRSplit\bin\SqlCLRClassLibrary.dll
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
We will use the above assembly path while creating the assebmly in MS SQL Server 2008 database.
CLR Split String Function T-SQL Code
SELECT * FROM sys.assemblies
Probably if you have not created your CLR assembly in your SQL Server database before, you will not see your CLR project in the sys.assemblies view.
CREATE ASSEMBLY SqlCLRClassLibrary FROM
'C:\VS\2008\CLR-Split\CLRSplit\bin\SqlCLRClassLibrary.dll'
WITH PERMISSION_SET = SAFE
Now you should see your assembly in the sys.assemblies system view.
CREATE FUNCTION CLRSplitStringFunction (
@String nvarchar(max),
@Seperator nvarchar(max),
@RemoveEmpty bit
)
RETURNS TABLE (
string nvarchar(max)
)
AS EXTERNAL NAME
SqlCLRClassLibrary.[CLRFunctions.SQLCLRFunctions].SplitString;
--AssemblyName.[RootNamespace.ClassName].SQLFunctionName
GO
Now we can use this sql clr function in a sample t-sql code.
SELECT * FROM dbo.CLRSplitStringFunction('1235-456--446- 5 ','-',1)
SELECT * FROM dbo.CLRSplitStringFunction('1235-456--446- 5 ','-',0)
|