hash

Produces a fixed-length hash value expression.

Syntax

hash(expression , [algorithm])

Parameters

expression – 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.

algorithm – 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)

Examples

Example 1 – 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')
```

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.