Produces a fixed-length hash value expression.
hash(expression , [algorithm])
is the value to be hashed. This can be a column name, variable, constant expression, or any combination of these that evaluates to a single value. It cannot be image, text, unitext, or off-row Java datatypes. Expression is usually a column name. If expression is a character constant, it must be enclosed in quotes.
is the algorithm used to produce the hash value. A character literal (not a variable or column name) that can take the values of either md5 or sha1, 2 (meaning md5 binary), or 3 (meaning sha1 binary). If omitted, md5 is used.
Algorithm |
Results in |
|---|---|
hash(expression, ‘md5’) |
A varchar 32-byte string. md5 (Message Digest Algorithm 5) is the cryptographic hash function with a 128-bit hash value. |
hash(expression) |
A varchar 32-byte string |
hash(expression, ‘sha1’) |
A varchar 40-byte string sha1 (Secure Hash Algorithm) is the cryptographic hash function with a 160-bit hash value. |
hash(expression, 2) |
A varbinary 16-byte value (using the md5 algorithm) |
hash(expression, 3) |
A varbinary 20-byte value (using the sha1 algorithm) |
This example shows how a seal is implemented. The existence of a table called “atable” and with columns id, sensitive_field and tamper seal.
update atable set tamper_seal=hash(convert(varchar(30), id) + sensitive_field+@salt, 'sha1')
When specified as a character literal, algorithm is not case-sensitive—“md5”, “Md5” and “MD5” are equivalent. However, if expression is specified as a character datatype then the value is case sensitive. “Time,” “TIME,” and “time” will produce different hash values.
If algorithm is a character literal, the result is a varchar string. For “md5” this is a 32-byte string containing the hexadecimal representation of the 128-bit result of the hash calculation. For “sha1” this is a 40-byte string containing the hexadecimal representation of the 160-bit result of the hash calculation.
If algorithm is an integer literal, the result is a varbinary value. For 2, this is a 16-byte value containing the 128-bit result of the hash calculation. For 3, this is a 20-byte value containing the 160-bit result of the hash calculation.
Trailing null values are trimmed by Adaptive Server
when inserted into varbinary columns.
Individual bytes that form expression are fed into the hash algorithm in the order they appear in memory. For many datatypes order is significant. For example, the binary representation of the 4-byte INT value 1 will be 0x00, 0x00, 0x00, 0x01 on MSB-first (big-endian) platforms and 0x01, 0x00, 0x00, 0x00 on LSB-first (little-endian) platforms. Because the stream of bytes is different between platforms, the hash value is different as well. Use hashbytes function to achieve platform independent hash value.
The hash algorithms MD5 and SHA1 are no longer considered
entirely secure by the cryptographic community. As for any such
algorithm, you should be aware of the risks of using MD5 or SHA1
in a security-critical context.
SQL92- and SQL99-compliant
Any user can execute hash.
See also hashbytes for platform independent hash values.
