MS SQL SHASum Support

30. January 2011 15:02

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

E-mail Kick it! DZone it! del.icio.us Permalink


Pingbacks and trackbacks (1)+