Official definition functions:
Hashbytes ( '<algorithm>', { @input | 'input' } )
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1
Parameter explanation:
Algorithm: Identifies the hashing algorithm used to perform a hash operation on the input. This is a required parameter with no default value. You need to use single quotation marks.
@input: The data type is varchar, nvarchar, or varbinary
'input' : string data
return value:
varbinary (maximum 8000 bytes)
Note: The allowable input value is limited to 8,000 bytes. The output conforms to the algorithm standard: MD2, MD4, and MD5 are 128 bits (i.e. 16 bytes); SHA and SHA1 are 160 bits (20 bytes)
One: Example MD5 encryption:
A complete notation:
PRINT LOWER (sys.fn_varbintohexstr (hashbytes (' MD5 ', ' This string needs to be encrypted ' )) >> 8926206f9ebb0bee4bbb0724fccb4410
Gradually split below
1. First use the Hashbytes function to see the resulting results
PRINT hashbytes (' MD5 ', ' This string needs to be encrypted ') >> 0x8926206f9ebb0bee4bbb0724fccb4410
Because the value returned by the function is a binary varbinary data , not a string cannot be used directly, so it is converted to a string
2. Using Sys.fn_varbintohexstr (@pbinin)
@pbinin: varbinary (max) data type
return: nvarchar data type
PRINT sys.fn_varbintohexstr (hashbytes (' MD5 ', ' This string needs to be encrypted ')) >> 0x8926206f9ebb0bee4bbb0724fccb4410
3. How to get 32-bit MD5 value, seemingly the largest can only to 32 bits, using the right function to intercept
PRINT Right (Sys.fn_varbintohexstr (' Hashbytes (' MD5 ', ' This string needs to be encrypted '))>> 8926206f9ebb0bee4bbb0724fccb4410
function definition
)
Parameters:
Character_expression: Can be a character or binary data expression, constant, variable, column, any data type that can be implicitly converted to varchar or nvarchar (text,ntext requires a display conversion)
Integer_expression: A positive integer that will return the number of characters
Return value: character_expression returns varchar for non-Unicode and returns nvarchar if Unicode
4. If you need uniform capitalization, use lower (character_expression ), Upper (character_expression ) to convert
Two: about the character encoding problem of MD5 encryption
A couple of comparisons first.
1. Character "È"
sqlserver:c05fc85230c60ed8fecb3670e77e603d
Result: Corresponds to C # gb2312
2. Character "This is kanji"
sqlserver:e8c9ab48b603f81b87f4ed4f1005bb6e
Result: Corresponds to C # gb2312
3. Character "123456"
sqlserver:e10adc3949ba59abbe56e057f20f883e
Results: Corresponds to C # gb2312,utf-8, Utf-7, ASCII
4. Character "Pxs123"
Sqlserver:684d95499489435c9351efcdd68b25f2
Results: Corresponds to C # gb2312,utf-8, Utf-7, ASCII
4. Character "[email protected]#."]
sqlserver:fc05d7826726e773e993bdca88f6b181
Result: With C # gb2312,utf-8, ASCII corresponds
5. Character English symbol "[email protected]#.&*) |_;"
sqlserver:4c782d772102482d09696d11bde1a02a
Result: With C # gb2312,utf-8, ASCII corresponds
6. Character character English mixed symbol "! )*、“
sqlserver:1a891b7b5b3285925e2783694c2a367c
Result: Corresponds to C # gb2312
6. Character character English mixed symbol "Chinese character Hanzi"
Sqlserver:49212c24bdb2fa254880119a332f86d6
Result: Corresponds to C # gb2312
8. Character "プログラミン"
Sqlserver:045b556a9cf5f0fe92ec7a7c9b3f66ca
Result: Corresponds to C # gb2312
In contrast:
SQL Server MD5 encryption function, the character of non-English characters will be encoded in accordance with the gb2312 method to calculate the encryption value, then C#,JS and other calculations MD5 encryption value is required to gb2312 encoding, in order to produce the same result.
If you are English characters, you can use Utf-8 to edit them.
^ If there is any mistake, please correct me! ^
SQL Server for MD5 encryption