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.

Read syntax diagramSkip visual syntax diagramHASH_CRC32HASH_MD5HASH_SHA1HASH_SHA256(‚ÄČexpression)

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 232 BINARY(4)
HASH_MD5 MD5 128 bit 2128 BINARY(16)
HASH_SHA1 SHA1 160 bit 2160 BINARY(20)
HASH_SHA256 SHA2-256 256 bit 2256 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'