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 (X100 ONLY)
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 (X100 ONLY)
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 (X100 ONLY)
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 (X100 ONLY)
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 (X100 ONLY)
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 (X100 ONLY)
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.
MD5SUM (HEAP ONLY)
MD5SUM(c1)
Result type: CHAR(32)
Returns the calculated MD5 message digest (also known as md5sum) of c1. The computed sum generated is a lowercase hexadecimal string.
SELECT MD5SUM('a') returns '0cc175b9c0f1b6a831c399e269772661'
SHA1SUM (HEAP ONLY)
SHA1SUM(expr)
Operand type: Any
Result type: CHAR(40)
Returns the SHA-1 checksum for the input data.
SHA224SUM (HEAP ONLY)
SHA224SUM(expr)
Operand type: Any
Result type: CHAR(56)
Returns the SHA-224 checksum for the input data.
SHA256SUM (HEAP ONLY)
SHA256SUM(expr)
Operand type: Any
Result type: CHAR(64)
Returns the SHA-256 checksum for the input data.
SHA384SUM (HEAP ONLY)
SHA384SUM(expr)
Operand type: Any
Result type: CHAR(96)
Returns the SHA-384 checksum for the input data.
SHA512SUM (HEAP ONLY)
SHA512SUM(expr)
Operand type: Any
Result type: CHAR(128)
Returns the SHA-512 checksum for the input data.