hash_nvarchar() function

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

This function provides 2¹²⁸- 2²⁵⁶ distinct return values and is intended for cryptographic purposes.

Before using the hash_nvarchar() function, be aware of the following behaviors:

The hash_nvarchar() function is generally much slower than the hash4() or hash8() function at performing calculations. Also, the hash_nvarchar() 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_nvarchar() function.
Columns with hash values from the hash_nvarchar() function cannot be zone mapped and do not use other Netezza Performance Server query performance techniques.

Syntax

The hash_nvarchar() function has the following syntax:

varchar = hash_nvarchar(varchar data [, int algorithm]);
varchar = hash_nvarchar(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

Both 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.

The hash_nvarchar() function is similar to the hash() function, but the hash_nvarchar() function returns a varchar data type for either varchar or nvarchar inputs. The hash() function returns an nvarchar data type for input nvarchar fields. If you are hashing nvarchar input strings, use the hash_nvarchar() function.

Returns

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