Adding a SHASUM function to MS SQL

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = false)]
    public static SqlString SHASum(SqlString S1)
    {
        byte[] buffer = Encoding.ASCII.GetBytes(S1.Value);
        SHA1CryptoServiceProvider cryptoTransformSHA1 =
        new SHA1CryptoServiceProvider();
        string hash = BitConverter.ToString(
            cryptoTransformSHA1.ComputeHash(buffer)).Replace("-", "");

        return new SqlString(hash);
    }
}

You will also need the following to install the function

CREATE Function SHASum(@S1 nvarchar(512))
	RETURNS nvarchar(64) AS EXTERNAL NAME SqlExtensions.UserDefinedFunctions.MD5Sum
GO

You should be able to call the function in normal sql code like SELECT dbo.SHASum('test value')

More about enabling .NET CLR In SQL Server

Adding an MD5SUM function to MS SQL.

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = false)]
    public static SqlString MD5Sum(SqlString S1)
    {
        MD5 md5 = System.Security.Cryptography.MD5.Create();
        byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(S1.Value);
        byte[] hash = md5.ComputeHash(inputBytes);

        // step 2, convert byte array to hex string
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < hash.Length; i++)
            sb.Append(hash[i].ToString("X2"));

        return new SqlString(sb.ToString());
    }
}

You will also need to following SQL To install the function

CREATE Function MD5Sum(@S1 nvarchar(512))
	RETURNS nvarchar(64) AS EXTERNAL NAME SqlExtensions.UserDefinedFunctions.MD5Sum
GO

You should be able to call the function in normal sql code like SELECT dbo.MD5SUM('test value')

More about enabling .NET CLR In SQL Server

Something that annoyed me slightly when moving from mysql to mssql was the complete lack of natural RegEx support. Here is some code that will enable you to use enable RegEx support in MS SQL. It will work with both 2005 and 2008.

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlBoolean RegEx(SqlString Value, SqlString Pattern)
    {
        if (Pattern.IsNull || Value.IsNull)
            return false;

        return Regex.IsMatch(Value.ToString(), Pattern.ToString());
    }
}

To install the function you will need to enable CLR support compile and install the assembly. Then you will need the following sql

CREATE Function RegEx(@Input nvarchar(512), @Pattern nvarchar(512))
	RETURNS BIT AS EXTERNAL NAME SqlExtensions.UserDefinedFunctions.RegEx
GO

You should be able to call the function in normal sql code like SELECT dbo.RegEx('value', '/vali/')

MS SQL Support functions that can be created in C# or VB.NET but it needs to be turned on in order to map the functions. This is really quite easy to do.

Simply runt he following sql to enable CLR support.

sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
GO

Now that it has CLR support turned on you can bring in your own custom function from dll files created from Visual Studio. Using sql like the following

CREATE ASSEMBLY SqlExtensions
	FROM 'C:\Temp\MyExtenstion.dll'

You can then create a function on a data for use like the following.

CREATE Function RegEx(@Input nvarchar(MAX), @Pattern nvarchar(MAX))
	RETURNS BIT AS EXTERNAL NAME SqlExtensions.UserDefinedFunctions.RegEx
GO

This can then be used in your normal sql in the following way.

SELECT * FROM TableName WHERE RegEx(column, 'Hello World') = 1

There is more information about creating and compiling a suitable project here

Here is a really simple function to extend mssql. It will save you writing LTRIM(RTRIM(data)) all over the place.

Simply create it using the following code;

CREATE FUNCTION dbo.trim(@str varchar(MAX))
	RETURNS varchar(MAX)
BEGIN
	RETURN LTRIM(RTRIM(@str))
END
GO

Then you can use it like this:

SELECT dbo.TRIM(' Hello World  ')