SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


SQL Server CLR Split String Function for 2-Dimensional Array

Transact-SQL programmer can develop assemlies in VB.NET or in C# and create CLR functions in SQL Server to split string expressions with better performance. This SQL Server CLR tutorial shows how to create a SQL CLR function which returns a table by splitting a two dimensional input string.

Before going further in this SQL tutorial, programmers and developers can also review SQL CLR function to split one dimension value list concatenated by a single separator character in referred SQL tutorial.

To make it easier for visualizing the way SQL developers and database administrators can use the SQL Server CLR function developed later in this tutorial, let's give an example. I've a string which forms a two-dimensional array of key value pairs expressing SQL Server versions and their RTM releases. I want to list SQL Server versions and corresponding build number side by side just like a table. Here is the SQL script where I used the SQL Server CLR function which is created at the end of this tutorial.

declare @concatenatedStringArray nvarchar(max)
set @concatenatedStringArray = N'
SQL Server 2016/13.00.1601.5;SQL Server 2014/12.0.2000.8;
SQL Server 2012/11.0.2100.60;SQL Server 2008 R2/10.50.1600.1;
SQL Server 2008/10.00.1600.22;SQL Server 2005/9.00.1399'
select * from KodyazSQLCLR2DSplitFunction(@concatenatedStringArray, ';', '/',0)

Output of executing above SQL code is as seen in below screenshot.

SQL Server CLR function to split concatenated row-column values
Sample SQL Server CLR split function to convert concatenated string expression into tabular form back


Develop SQL Assembly Project for CLR Function in Visual Studio

Launch Visual Studio.
Create a new project by following menu options:
File > New > Project...
From the installed templates, you can use Visual C# or Visual Basic Windows Class Library project templates.

As you see below, I named my sample SQL CLR project as KodyazSQLCLR2DSplit and chosed VB.NET for the class library development in Visual Studio 2015

create SQL Server CLR function in Visual Studio

Now replace the below VB.NET class library code...

Public Class Class1

End Class

With following code

Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Public Class SQLCLRFunctions
 Private Shared _ColumnSeperator As SqlString
 Private Shared _Id As SqlInt32

 <SqlFunction(FillRowMethodName:="FillRowMethod", TableDefinition:="id int, column1 nvarchar(max), column2 nvarchar(max)")>
 Public Shared Function SplitString(ByVal InputString As SqlString, ByVal RowSeperator As SqlString, ByVal ColumnSeperator As SqlString, ByVal RemoveEmpty As Boolean) As IEnumerable

  _ColumnSeperator = ColumnSeperator
  _Id = 0

  Dim DelimitersList() As String
  ReDim DelimitersList(0)
  DelimitersList(0) = RowSeperator.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 Id As SqlInt32, ByRef Col1 As String, ByRef Col2 As String)
  Dim colArray() As String
  colArray = row.Split(_ColumnSeperator)
  _Id = _Id + 1
  Id = _Id
  Col1 = colArray(0)
  If colArray.Length > 1 Then
   Col2 = colArray(1)
  End If
 End Sub

End Class

Now build your Visual Studio Assembly library solution for our SQL split string CLR function.

When you build or rebuild the solution in the Output screen, the path where assembly is generated is displayed.
Copy this assembly path

SQL Server CLR function class library dll file path
C:\DevProjects\VS Projects\KodyazSQLCLR2DSplit\KodyazSQLCLR2DSplit\bin\Release\KodyazSQLCLR2DSplit.dll

When you are developing for the first time, you can build your assembly in Debug mode.
But when you are finished with all coding and test, rebuild your solution in Release mode for a better performance.


Create CLR Assembly and CLR Function in SQL Server

Launch SQL Server Management Studio (SSMS) and connect to the database where you want to create CLR function using recently created class library in Visual Studio.

Before progressing more in the tutorial, to prevent possible errors we have to set database trustworthy property to on by executing following SQL Server command

ALTER DATABASE [kodyaz.test] SET TRUSTWORTHY ON

set SQL Server database trustworthy property

Otherwise, while creating our CLR assembly, SQL developers can experience below error

Msg 10327, Level 14, State 1, Line 15
CREATE ASSEMBLY for assembly 'KodyazSQLCLR2DSplit' failed because assembly 'KodyazSQLCLR2DSplit' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

The reason is, in our SQL CLR tutorial we have created a class library where we have used one or more public or shared class properties.
In order to use these class property variables in the SQL Server CLR function, we need to set the permission level to UNSAFE for our CLR assembly

In fact these properties are for column seperator (_ColumnSeperator) and for the order number (_Id) of returning row.

So first of all execute the "ALTER DATABASE [kodyaz.test] SET TRUSTWORTHY ON" statement with replacing my sample database name with your target dbname.

As the second step in our CLR tutorial, execute Create Assembly statement

CREATE ASSEMBLY KodyazSQLCLR2DSplit FROM
'C:\DevProjects\VS Projects\KodyazSQLCLR2DSplit\KodyazSQLCLR2DSplit\bin\Release\KodyazSQLCLR2DSplit.dll'
WITH PERMISSION_SET = UNSAFE

The path is the file path you have your class library .dll file.
You are free to name your assembly.

You cannot create a second SQL Server assembly record using the same class library .dll file. But you can first delete the existing one with Drop Assembly command

DROP ASSEMBLY KodyazSQLCLR2DSplit

SQL Server database administrator and Transact-SQL developers can query assemblies system table to see existing assembly entries and your new assembly status.

SELECT * FROM sys.assemblies

list of assemblies registered to SQL Server database

Now we are ready to create SQL CLR function for splitting string expressions formed of rows and columns

CREATE FUNCTION KodyazSQLCLR2DSplitFunction (
 @String nvarchar(max),
 @RowSeperator nvarchar(max),
 @ColumnSeperator nvarchar(max),
 @RemoveEmpty bit
)
RETURNS TABLE (
 id int,
 column1 nvarchar(max),
 column2 nvarchar(max)
)
AS EXTERNAL NAME
 KodyazSQLCLR2DSplit.[KodyazSQLCLR2DSplit.SQLCLRFunctions].SplitString;
 --AssemblyName.[RootNamespace.ClassName].SQLFunctionName
GO

SQL CLR function which returns a table of three columns

Please note followings, in the above SQL command:
KodyazSQLCLR2DSplit is the assembly name,
[KodyazSQLCLR2DSplit.SQLCLRFunctions] is the class name we have created in Visual Studio solution including its namespace,
SplitString is the function name in our class library.


SQL CLR Split String Function Sample Cases

Let's execute new SQL CLR split string function on sample cases. Following case is showing a string expression formed of a concatenated key-value pairs. Let's use our SQL CLR function to split the concatenated string into rows formed of key and its value in seperate columns.

select * from KodyazSQLCLR2DSplitFunction( 'A;SQL Server|B;T-SQL|C;CLR', '|', ';', 0)

SQL Server CLR function for splitting concatenated key-value pairs

Here is an other example. This time I split a string expression which contains firstname and lastname of a list of users seperated with semi-column

select * from KodyazSQLCLR2DSplitFunction( N'Eralper Yılmaz;Darth Vader;Kit Walker', ';', ' ',0)

split fullname using SQL CLR function


Download Visual Studio Source Project for SQL Server CLR Function

SQL developers can download sample SQL CLR project and use it for their own CLR function by enhancing the current one

Additionally if you are interested with more samples for creating CLR functions, you can review SQL CLR tutorial: Generate Random Integer Number using SQL Server CLR Function.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.