Always wanted to escape the set based world of SQL and get back to that good old .Net procedural (even object oriented) experience for a quick calculation? Let me show you how to get best of both worlds with this example. Of course you can use Common Language Runtime functions for all kinds of algorithms, but below I explain how to do base36 conversion in .Net called from SQLServer.

Turning a base36 “number” into a decimal number is not supported in TSQL out of the box. You’ll need some complex querying to replicate that. Needless to say it is probably not the most efficient code to run. This type of number crunching is then better offloaded to an assembly. An assembly is a DLL library which you load into your SQLServer database. The library can have all kind of support functions. I can even connect to your database internally and work with that data. Another option is to pass parameters into the function call and avoid the overhead of querying. All depends on your use case.

Let’s use the standard approach to this, where I just want to pass in a base36 number and convert it to its decimal value. To read more about base36, have a look on this wiki page. It’s basically another representation of a number, but instead of using a base 2 (binary), a base 10 (decimal) or a base 16 (hexadecimal), you use a base 36 (10 digits and 26 characters), to basically same on space to represent a number.

e.g. decimal 100 is represented as 2S in base36. 1 character down. but 100.000.000 is just 1NJCHS, or 3 characters profit.

The steps to follow are:

  • C# code file with the algorithm
  • Compile code to DLL
  • Prepare assembly
  • Create assembly
  • Create function
  • Call the function

C# code file – base36.cs

Create a simple code in Notepad (or Visual Studio Code if you prefer to be modern) file with following content:

public class T  
{
    private static int val(char c)
    {
	    if (c >= '0' && c <= '9')
		    return (int)c-'0';
	    else
		    return (int)c-'A'+10;
    }

    public static int Base36ToDecimal(string base36)  
    {
    	int power = 1;
	int num = 0;
 
	for (int i = str.Length - 1; i >= 0; i--)
	{
	    if (val(str[i]) >= 36)
	      return -1;

    	    num += val(str[i]) * power;
	    power = power * 36;
	}

    	return num;
    }
}

Basically, you need a public static function that returns something when creating a scalar valued function.

You can add a public void Main() function if you want to compile it to test it out.

Compile – base36.dll

To compile the file, make sure you have a .net framework installed and run

C:\Windows\Microsoft.NET\Framework\v4.0.30319\csc.exe /t:library /out:base36.dll base36.cs

Prepare Assembly

You have the DLL, and jsut need to load it into SQLServer. Place it in a directory to which the account running your SQLServer instance has full access. e.g. C:\assemblies\

If you create a new directory, go to security and set the rights up for that account.

Create Assembly in database

CREATE ASSEMBLY [base36] FROM 'C:\assemblies\base36.dll'
WITH PERMISSION_SET = SAFE

You just made the assembly available in the database. The name of the assembly doesnt have to be the same as the dll name or the class.

Create Function

In order to use the functions in the assembly, we’ll have to reference them from TSQL by creating a function object in the database. Other routines in TSQL can then call this function.

CREATE FUNCTION Base36ToDecimal(@str NVARCHAR(100)) RETURNS INT   
AS EXTERNAL NAME Base36.T.Base36ToDecimal;

You can give it any name, but you have to reference the function name as it appears in your class code. Also the signature has to be the same as defined in the class. Here you can see I defined a parameter and it returns an int.

Cherry on the cake (also starts with C as the other headers)

Call the function:

SELECT dbo.Base36ToDecimal('2S')

-- or
SELECT dbo.Base36ToDecimal(base36_field) from bigtable

This will now calculate in the external library the conversion and return the result in SQLServer.