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 have a look at http://www.stev.org/post/2011/01/30/Enabling-MSSQL-CLR-Functions.aspx





Last Modified: 21 December 2016

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-08-29 - C Sharp - StopWatch, The high resolution timer
2011-08-10 - C Sharp - MD5Sum / SHASum
2011-08-09 - MSSQL - Last Backup time and size
2011-07-22 - CSharp - Fibonacci sum
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-22 - CSharp - Extending any class
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-03 - MSSQL - Who locks what
2011-05-05 - MSSQL - Log Sizes
2011-03-07 - C Sharp - Windows Logoff
2011-03-02 - ASPNET - Dynamic Controls
2011-03-01 - C Sharp - Resize an image by size of height
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - All temp tables are global
2011-02-10 - CSharp - Shutdown or reboot windows
2011-02-06 - C Sharp - Gridview Bound Yes No
2011-02-05 - MSSQL - Levenshtein
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - Adding MD5 checksum support