Is there an equivalent to SHA1() in MS-SQL?
Solution 1
SQL Server 2005 and later has the HashBytes() function.
Solution 2
If you want to get a SHA1 hash exactly as MySQL would generate it (i.e. as a varchar), you can combine HashBytes with sys.fn_varbintohexsubstring. E.g.
SELECT sys.fn_varbintohexsubstring(0, HashBytes('SHA1', 'password'), 1, 0)
See http://accessrichard.blogspot.co.nz/2010/12/sql-server-and-net-equivalent-to-php.html for more details.
Solution 3
From google groups - A Possibility
Solution 4
You may also want to check out http://www.stev.org/post/2011/01/30/MS-SQL-SHASum-Support.aspx you should be able to modify it to produce anything you want. Though some c# coding may be required.
Solution 5
MSSQL Server
HASHBYTES('SHA1', CAST('abcd@#' as nvarchar(max)))
CONVERT(VARCHAR(MAX), HASHBYTES('SHA1', CAST('abcd@#' as nvarchar(max))) , 2)
/* result */
0x77DD873DBAB2D81786AB9AE6EA91B1F59980E48C
77DD873DBAB2D81786AB9AE6EA91B1F59980E48C
C#
using (SHA1Managed sha1 = new SHA1Managed())
{
string input = "abcd@#";
var hash = sha1.ComputeHash(Encoding.Unicode.GetBytes(input));
var sb = new StringBuilder(hash.Length * 2);
foreach (byte b in hash)
{
sb.Append(b.ToString("X2")); // can be "x2" if you want lowercase
}
return sb.ToString();
}
//result "77DD873DBAB2D81786AB9AE6EA91B1F59980E48C"
GEOCHET
I am an EE by day, and a SE moonlighter. Find me on IRC @ #SOMafia on Slashnet. Vote Welbog for moderator!
Updated on January 28, 2021Comments
-
GEOCHET over 3 years
Converting a couple stored procedures from MySQL to Microsoft SQL server. Everything is going well, except one procedure used the MySQL
SHA1()
function. I cannot seem to find an equivalent to this in MS-SQL.Does anyone know a valid equivalent for
SHA1()
on MS-SQL? -
GEOCHET over 15 yearsLooks perfect, answered while I took a leak. I love stackoverflow. Thanks!
-
Joel Coehoorn over 15 yearsNow if they'd just get support for a 256-bit algo instead of only 160