Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

CLR String Sort Function in SQL Server


By:   |   Last Updated: 2018-05-29   |   Comments (7)   |   Related Tips: 1 | 2 | More > Functions - User Defined UDF

Problem

With the introduction of SQL Server 2005, Microsoft released the Common Language Runtime (CLR) to allow developers and DBAs to take advantage of managed code outside of SQL Server.  The original thought when this was announced was this was going to be a bad thing, because people that knew how to develop in a .NET language, but not T-SQL, would adopt this across the board.  Over the past couple of years the opposite has happened.  T-SQL still continues to be the primary language that is used and I have seen very few implementations where the CLR is being used.  In this tip we will take a look at a simple example of where the CLR can come in handy and what a big improvement it can make on certain tasks.

Solution

Before we get started the first thing that needs to be done is that you need to enable the CLR on your SQL Server.  This can be done by using the code below. 

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'clr enabled', 1;  
GO  
RECONFIGURE;  
GO

With the CLR you can create both CLR functions, stored procedures, etc.  For this example we are going to create a CLR function that takes a string and parses and sorts the data.  So for instance, let's say we have data such as the following stored in a database table:

apple,pear,orange,banana,grape,kiwi

and we want the results sorted as follows:

apple,banana,grape,kiwi,orange,pear

We could write a SQL Server function to do this by parsing the string and storing the results in a temporary table and then retrieve the results in a sorted order and then put the string back together again or we could write a short CLR function to do the same exact thing.

Let's take a look at how this could be done with a CLR function.

Step 1 - CLR code

The first thing we need to do is to write the CLR code for this.  This could be written in either C#.NET or VB.NET.  In this example we will use VB.NET.

The following code has a Class (CLRFunctions) and a Function (SortString).  The function takes a string variable and returns a string variable.  There are few built-in VB functions that are used the first Split that splits the incoming string into an array and the second Array.Sort that sorts the data in the array.  This is a lot easier then writing T-SQL code to do the same thing.

Copy and save the code below in a file called: C:\SQLServerCLRSortString.vb

Public Class CLRFunctions    
    Public Shared Function SortString(ByVal Name As String) As String   
        Dim i As Integer 
        Dim returnValue As String 
        Dim stringArray() As String 
         
   ' split string into an array        
   stringArray = Split(Name, ",") 
    
   ' sort array values 
   Array.Sort(stringArray) 
    
   ' recreate string 
   returnValue = "" 
    
   For i = LBound(stringArray) To UBound(stringArray) 
       returnValue = returnValue & stringArray(i) & "," 
   Next i 

   Return returnValue 

    End Function   
End Class 

Step 2 - Compile CLR Code

In order to use this code, the code has to be compiled first. 

The following command is run from a command line to compile the CLR code using the vbc.exe application.  This is found in the .NET 2.0 framework directory.  This may be different on your server or desktop.  Also, this code should be compiled on the machine where the code will run.

So from a command line run a command such as the following:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc /target:library C:\SQLServerCLRSortString.vb

The code should now be compiled in a file called: C:\SQLServerCLRSortString.dll

Step 3 - Create Assembly and Function

After the code has been compiled you need to create the assembly and the function with SQL Server.  To do this, run these commands in the database where you want to use the function. 

The assembly ties an internal object to the external DLL that was created and the function is similar to a normal SQL Server function.

For the function you will see three components that are referenced CLRFunctions.CLRFunctions.SortString.

  • CLRFunctions - the assembly reference
  • CLRFunctions - the class reference in the VB code
  • SortString - the function reference in the VB code
CREATE ASSEMBLY CLRFunctions FROM 'C:\SQLServerCLRSortString.dll'  
GO 

CREATE FUNCTION dbo.SortString    
(    
 @name AS NVARCHAR(255)    
)     
RETURNS NVARCHAR(255)    
AS EXTERNAL NAME CLRFunctions.CLRFunctions.SortString 
GO 

Step 4 - Create Test Table and Data

To test this you can create a sample table and some test data as shown below.

CREATE TABLE testSort (data VARCHAR(255)) 
GO

INSERT INTO testSort VALUES('apple,pear,orange,banana,grape,kiwi') 
INSERT INTO testSort VALUES('pineapple,grape,banana,apple') 
INSERT INTO testSort VALUES('apricot,pear,strawberry,banana') 
INSERT INTO testSort VALUES('cherry,watermelon,orange,melon,grape') 

Step 5 - Test It

To test the function, run the following SELECT statement based on the sample table and data created above. This will show the before and after data.

SELECT data, dbo.sortString(data) as sorted FROM testSort 

Here is the output from the above query showing the data before and after the sort.

query output

Step 6 - Cleanup

To get rid of the code you will need to delete the DLL that is created from the compile step as well as the VB file that was created.

In addition, run this T-SQL code to drop the objects that were created.

DROP FUNCTION dbo.SortString  
GO 
DROP ASSEMBLY CLRFunctions 
GO 
DROP TABLE testSort 
GO 

Summary

That's all there is to creating a CLR function.  This was a pretty simple example, but should give you an idea on how to move forward.  Once you have started using this you will see the advantages and also how much quicker some tasks run using the CLR vs T-SQL code.  The code that was written was pretty basic, but shows how this can work.

This code has been tested successfully with SQL Server 2017, so should work fine with all versions that support CLR.

Next Steps
  • Give this example a try and see what other functions you could write that could take advantage of the CLR
  • If you don't know how to write either VB or C# now is the time to begin learning.  You will find a lot of things these languages will make a lot easier to implement.


Last Updated: 2018-05-29


next webcast button


next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, July 23, 2012 - 1:32:01 PM - Blas Back To Top

Public Class CLRFunctions

PublicSharedFunction fcuentaArc(ByVal ruta AsString) AsInteger

Dim ret AsInteger

Dim Archivo AsString

Archivo = Dir(ruta.trim)

DoWhile Archivo <> ""

ret = ret + 1

Archivo = Dir()

Return ret

EndFunction

End Class


Friday, July 20, 2012 - 2:26:40 PM - Greg Robidoux Back To Top

Blas - what is your function trying to do?


Friday, July 20, 2012 - 12:54:27 PM - Blas Back To Top

hello help me with this error, please

Mens. 6522, Level 16, State 2, Line 1 Error. NET Framework error occurred during execution of the routine or user-defined aggregate "fcuentaArc" System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version = 2.0.0.0, Culture = neutral, PublicKeyToken = b77a5c561934e089' failed. System.Security.SecurityException:    at CLRFunctions.fcuentaArc (String path)


Wednesday, July 13, 2011 - 8:05:11 AM - Paul Back To Top

Hi Greg,

Just come across this article you wrote - I know nothing about CLR, but your article is clear, simple and to the point - perfect - and best of all, the example you give works and is totally understandable.

I thought I was going to have to purchase Visual Studio, probably Professional edition, but no, all you need is notepad!

Thanks!
Paul


Monday, July 04, 2011 - 8:10:42 AM - Mark Hutchinson Back To Top

The complementary function of Split() is Join().  As your string lengths increase, you will find that the Join() is much more efficient than iterative concatenation.


Thursday, February 17, 2011 - 2:48:35 PM - Greg Robidoux Back To Top

Are you referring to this tip or to the tip for sending email using CLR: http://www.mssqltips.com/tip.asp?tip=1795?


Monday, February 07, 2011 - 1:20:16 AM - sqlchild Back To Top

dear sir, i run your above query but it gives the following error:

Could not find Type 'SendEmail.StoredProcedure' in assembly 'SendEmail'.


Learn more about SQL Server tools