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.
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
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
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
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
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
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)
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)
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.