HASH_CRC32, HASH_MD5, HASH_SHA1, and HASH_SHA256

The hashing functions return a fixed-length value that is the result of applying a hash algorithm to an input argument. The functions are intended for cryptographic purposes.

The schema is SYSIBM.

expression: An expression that represents the string value that is to be hashed. The expression must return a built-in character string, graphic string, or binary string.

The result is produced by applying the hash algorithm to expression. The hash algorithm is determined by the name of the function that is invoked.

The result of the function is BINARY, and the length attribute of the result depends on the hash algorithm specified. The characteristics of the result are summarized in the following table.

Table 1. Characteristics of the result for each algorithm
Function name	Algorithm	Result size	Number of different values that can be returned	Hash function result data type
HASH_CRC32	CRC32	32 bit	2³²	BINARY(4)
HASH_MD5	MD5	128 bit	2¹²⁸	BINARY(16)
HASH_SHA1	SHA1	160 bit	2¹⁶⁰	BINARY(20)
HASH_SHA256	SHA2-256	256 bit	2²⁵⁶	BINARY(32)

If the first argument can be null, the result can be null. If the first argument is null, the result is the null value.

Note that security flaws have been identified in both the SHA1 and MD5 algorithms. You can find acceptable hash algorithms in applicable compliance documentation, such as National Institute of Standards and Technology (NIST) Special Publication 800-131A.

Notes

Syntax alternatives:

Hash functions HASH_MD5, HASH_SHA1, and HASH_SHA256 are similar to the following HASH function, where the hash algorithm is specified as an integer constant value of 0, 1, or 2:

HASH ( expression , integer-constant )

For these hash functions, invoking the HASH function for hashing is recommended to increase the portability of applications.

Examples

Example 1:

Invoke the HASH_CRC32 function to use the CRC32 algorithm to generate a hashed value.

SELECT HEX(HASH_CRC32('ABCDEFGHIJKLMNOPQRZTUVWXYZ'))
FROM SYSIBM.SYSDUMMYU;

The following value is returned:

X'B4B86309'

Example 2:

Invoke the HASH_MD5 function to use the MD5 algorithm to generate a hashed value.

SELECT HEX(HASH_MD5('ABCDEFGHIJKLMNOPQRZTUVWXYZ'))
FROM SYSIBM.SYSDUMMYU;

The following value is returned:

X'E433BC7BE26A152E54E2EA0C92778160'

Example 3:

Invoke the HASH_SHA1 function to use the SHA1 algorithm to generated a hashed value.

SELECT HEX(HASH_SHA1('ABCDEFGHIJKLMNOPQRZTUVWXYZ'))
FROM SYSIBM.SYSDUMMYU;

The following value is returned:

X'8F34563A0FA4BA1A285C8035935D010629385474'

Example 4:

Invoke the HASH_SHA256 function to use the SHA256 algorithm to generate hashed value.

SELECT HEX(HASH_SHA256('ABCDEFGHIJKLMNOPQRZTUVWXYZ'))
FROM SYSIBM.SYSDUMMYU;

The following value is returned:

X'403AC046B04F4A749E9810971083997B71F2B6FAF87CECCDE657E93FFCF700F0'