HASH scalar function

The HASH function returns a varying-length value that is the result of applying the specified algorithm to the first argument. The function is intended for cryptographic purposes.

Read syntax diagramSkip visual syntax diagramHASH( expression,0,algorithm)

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.
algorithm
An integer constant value of 0, 1, or 2 that indicates the hash algorithm to be used when the function name is HASH. If no algorithm is specified, the default value of 0 is used which indicates the MD5 algorithm.

The result is produced by applying the hash algorithm, algorithm, to expression.

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

Table 1. Characteristics of the result for each algorithm
Algorithm Value for algorithm parameter Result size Number of different values that can be returned HASH function result data type
MD5 0 128 bit 2128 VARBINARY(16)
SHA1 1 160 bit 2160 VARBINARY(20)
SHA256 2 256 bit 2256 VARBINARY(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.

Notes

Security considerations:
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.
Syntax alternatives:
The HASH function is similar to the other hashing functions, where the hash algorithm is specified as part of the function name. For example: 
HASH_MD5  (  expression  )

Invoking the HASH function for hashing is recommended to increase the portability of applications.

Examples

Example 1:
Invoke the HASH function to use the MD5 algorithm to generate a hashed value. 
SELECT HEX(HASH(’ABCDEFGHIJKLMNOPQRZTUVWXYZ’ , 0 )) 
FROM SYSIBM.SYSDUMMYU;

The following value is returned:

X’E433BC7BE26A152E54E2EA0C92778160’
Example 2:
Invoke the HASH_SHA1 function to use the SHA1 algorithm to generate a hashed value.
SELECT HEX(HASH(’ABCDEFGHIJKLMNOPQRZTUVWXYZ’, 1 )) 
FROM SYSIBM.SYSDUMMYU;

The following value is returned:

X’8F34563A0FA4BA1A285C8035935D010629385474’
Example 3:

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

SELECT HEX(HASH(’ABCDEFGHIJKLMNOPQRZTUVWXYZ’ , 2 )) 
FROM SYSIBM.SYSDUMMYU;

The following value is returned:

X’403AC046B04F4A749E9810971083997B71F2B6FAF87CECCDE657E93FFCF700F0’