hash

Produces a fixed-length hash value expression.

Syntax

hash(expression , [algorithm])

Parameters

Examples

Usage

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” 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.

Note: Trailing null values are trimmed by the SAP ASE 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.

Note: 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.

Standards

SQL92- and SQL99-compliant

Permissions

Any user can execute hash.

Related reference
hashbytes