hash() function

The hash() function returns a 128-bit, 160-bit, or 256-bit hash of the input data, depending on the algorithm that you select.

This function provides 2128- 2256 distinct return values and is intended for cryptographic purposes. The function almost never produces the same output for two different inputs.

Before using the hash() function, be aware of the following behaviors:
  • The hash() function is generally much slower than the hash4() or hash8() function at performing calculations. Also, the hash() function returns a 16 - 32 byte binary varchar value; comparing binary varchar values takes longer than comparing integer values and therefore increases query run times. If you need speed in hash generation and comparison or if all you need is a simple one-way lookup function, use the hash4() or hash8() function instead of the hash() function.
  • Columns with hash values from the hash() function cannot be zone mapped and do not use other Netezza Performance Server query performance techniques.

Syntax

The hash() function has the following syntax:
varchar = hash(varchar data [, int algorithm]);
nvarchar = hash(nvarchar data [, int algorithm]);

The data value specifies the varchar or nvarchar value to hash.

The algorithm value is an integer code, which defaults to 0. The available algorithms and the sizes of the resulting hash values are shown in the following table:

Table 1. Algorithms supported for cryptographic hashing
Code Description Result
0 MD5 128-bit hash
1 SHA-1 160-bit hash
2 SHA-2 256-bit hash

The MD5 algorithm and Secure Hash Algorithm (SHA) are message digest algorithms that are derived from MD4. The SHA hash functions are the result of an effort by the National Security Agency (NSA) to provide strong cryptographic hashing capabilities.

Important: To process nvarchar values, you should use the hash_nvarchar() function instead of the hash() function. The hash_nvarchar() function returns a varchar value for input nvarchar data. The hash() function remains for compatibility with earlier versions and results, but tables and views that store hash('NVARCHAR') function values might encounter problems during loads and GENERATE STATISTICS operations.

Returns

The function returns hashed data. The return type is a 16 - 32 byte binary varchar value.

Example

select hash('Netezza',0);
       HASH
------------------
Ã
®dïO=±NEÂ
ÂÂÃ
(1 row)