Hashing Functions
Hashing functions generate a fixed length “hash” value for given data. They are useful for summarizing a relatively large data item into a small fixed length representation that usually will be distinct from hashes of dissimilar data. Uniqueness is not guaranteed but the chances of two dissimilar items having the same hash value is very low.
HASH
HASH(expr)
Operand type: Any except long types
Result type: INTEGER4
Generates a four-byte numeric value from expressions of all data types except long types. The implicit size for the expression can affect the result.
SELECT HASH(1), HASH(int1(1)), HASH(int2(1)), HASH(int4(1))\g
returns the following single row:
Col1 Col2 Col3 Col4
--------------------------------------------------
-1489118143 1526341860 ‑1489118143 1711753094
Note: Because the constant 1 is implicitly a short integer, only the return values for HASH(1) and HASH(int2(1)) match. For the remaining columns, the difference in the number of bytes holding the integer leads to a different hash value. Also, the generated hash value is not guaranteed unique, even if the input values are unique.
MD5_HEX
MD5_HEX(expr)
Operand type: CHAR, VARCHAR, NCHAR, NVARCHAR, INTEGER1, INTEGER2, INTEGER4, INTEGER8
Result type: CHAR(32)
Calculates the MD5 (Message-Digest Algorithm 5) 128-bit hash value (message digest) of expr with respect to its binary representation as given by HEX(expr).
Returns the hash value as a lowercase hexadecimal string.
SHA1_HEX
SHA1_HEX(expr)
Operand type: CHAR, VARCHAR, NCHAR, NVARCHAR, INTEGER1, INTEGER2, INTEGER4, INTEGER8
Result type: CHAR(40)
Calculates the SHA-1 (Secure Hash Algorithm 1) 160-bit hash value (message digest) of expr with respect to its binary representation as given by HEX(expr).
Returns the hash value as a lowercase hexadecimal string.
SHA224_HEX
SHA224_HEX(expr)
Operand type: CHAR, VARCHAR, NCHAR, NVARCHAR, INTEGER1, INTEGER2, INTEGER4, INTEGER8
Result type: CHAR(56)
Calculates the SHA-2 (Secure Hash Algorithm 2) 224-bit hash value (message digest) of expr with respect to its binary representation as given by HEX(expr).
Returns the hash value as a lowercase hexadecimal string.
SHA256_HEX
SHA256_HEX(expr)
Operand type: CHAR, VARCHAR, NCHAR, NVARCHAR, INTEGER1, INTEGER2, INTEGER4, INTEGER8
Result type: CHAR(64)
Calculates the SHA-2 (Secure Hash Algorithm 2) 256-bit hash value (message digest) of expr with respect to its binary representation as given by HEX(expr).
Returns the hash value as lowercase hexadecimal string.
SHA384_HEX
SHA384_HEX(expr)
Operand type: CHAR, VARCHAR, NCHAR, NVARCHAR, INTEGER1, INTEGER2, INTEGER4, INTEGER8
Result type: CHAR(96)
Calculates the SHA-2 (Secure Hash Algorithm 2) 384-bit hash value (message digest) of expr with respect to its binary representation as given by HEX(expr).
Returns the hash value as lowercase hexadecimal string.
SHA512_HEX
SHA512_HEX(expr)
Operand type: CHAR, VARCHAR, NCHAR, NVARCHAR, INTEGER1, INTEGER2, INTEGER4, INTEGER8
Result type: CHAR(128)
Calculates the SHA-2 (Secure Hash Algorithm 2) 512-bit hash value (message digest) of expr with respect to its binary representation as given by HEX(expr).
Returns the hash value as lowercase hexadecimal string.
Last modified date: 06/28/2024