HASH function [String]

Returns the specified value in hashed form.

Syntax

HASH( string-expression[, algorithm ] )

Parameters

string-expression The value to be hashed. This parameter is case sensitive, even in case-insensitive databases.
algorithm The algorithm to use for the hash. Possible values include: MD5, SHA1, SHA1_FIPS, SHA256, SHA256_FIPS. By default, the MD5 algorithm is used.

Note

The FIPS algorithms are only for use on systems using FIPS 140-2 certified software from Certicom.

Returns

VARCHAR

Remarks

Using a hash converts the value to a byte sequence that is unique to each value passed to the function.

If the server was started with the -fips option, the algorithm used, or the behavior, may be different, as follows:

SHA1_FIPS is used if SHA1 is specified
SHA256_FIPS is used if SHA256 is specified
an error is returned if MD5 is specified

Following are the return types, depending on the algorithm used:

MD5 returns a VARCHAR(32)
SHA1 returns a VARCHAR(40)
SHA1_FIPS returns a VARCHAR(40)
SHA256 returns a VARCHAR(40)
SHA256_FIPS returns a VARCHAR(40)

Caution

All the algorithms are one-way hashes. It is not possible to re-create the original string from the hash.

Standards and compatibility

SQL/2003 Vendor extension.

Example

The following example creates a table called user_info to store information about the users of an application, including their user ID and password. One row is also inserted into the table. The password is hashed using the HASH function and the SHA256 algorithm. Storing hashed passwords in this way can be useful if you do not want to store passwords in clear text, yet you have an external application that needs to compare passwords.

CREATE TABLE user_info ( 
  employee_id   INTEGER NOT NULL PRIMARY KEY,
  user_name CHAR(80),
  user_pwd CHAR(80) );
INSERT INTO user_info 
  VALUES ( '1', 's_phillips', HASH( 'mypass', 'SHA256' ) );

Discuss this page in DocCommentXchange.
Send feedback about this page using email.