HASH( ) function

Returns a salted cryptographic hash value based on the input value.


HASH(field <,salt_value>)



Numeric, Character, Datetime, or Logical. The value to hash.


Optional. Numeric constant or Character. The salt value to use. You can specify a PASSWORD identifier number from 1 to 10, or a character string enclosed in quotation marks. If you omit the parameter, the ACL default salt value is used.

The salt value is limited to 128 characters, and is automatically truncated to 128 characters if you specify a longer salt value.




You can use the HASH( ) function to protect sensitive data, such as credit card numbers, salary information, or social security numbers. HASH( ) is a one-way encoding function. Data in clear text can be used to produce a hash value, however the hash value cannot subsequently be unencoded or decrypted.

A specific clear text value always produces the same hash value, so you can search a field of hashed credit card numbers for duplicates, or join two fields of hashed credit card numbers, and the results are the same as if you had performed the operation on the equivalent clear text fields.

To avoid storing sensitive data on a server, you can create a computed field locally using the HASH( ) function and then create a new table by extracting the hashed field and any other required fields, while excluding the clear text field. You can use the new table on the server for your analysis, and once you have the results, refer back to the original table if you need to see the clear text version of any of the hashed data. If storing sensitive data locally, beyond initial use, is prohibited, you can delete the original table after you have created the new table, and refer to the original source system for the clear text values.

In order to produce identical hash values, two clear text values must be exactly identical. For example, different hash values result from the same credit card number with or without hyphens, or the same name in title case or all upper case. You may need to incorporate functions such as INCLUDE( ), EXCLUDE( ), or UPPER( ) in the HASH( ) function to standardize clear text values. Leading and trailing blanks are automatically trimmed by the HASH( ) function, so there is no need to use the TRIM( ) or ALLTRIM( ) functions.

If you have data in which leading or trailing blanks represent meaningful differences between values you need to replace the blanks with another character before hashing the values. For example:

HASH(REPLACE(field_name, " ", "_"))

You can also use the HASH( ) function to check if blocks of text in two comment fields are identical. To perform this check, create two computed fields similar to the ones shown below, and then create a filter to find any text blocks that are not identical.

If the comment fields are in separate tables, create a computed HASH( ) field in each table and then use the computed fields as a common key field to do an unmatched join of the two tables. The records in the joined output table represent text blocks that are not identical.

The HASH( ) function uses an SHA-2 cryptographic hash algorithm that produces a fixed-length hashed output of 64 bytes, regardless of the length of the input value. The clear text input value can be longer than 64 bytes.

The salt value

The protection offered by the HASH( ) function is strengthened by the addition of a salt value prior to hashing. The salt value is an alphanumeric string that is concatenated with the source data value. The entire concatenated string is then used to produce the salted, hashed value. This approach makes the hashed values more resistant to decoding techniques.

A fixed, default salt value is automatically used unless you specify a salt value. You can use either of the following methods to specify a salt value:

The password method is intended for use in scripts that prompt for the password at the beginning of the script, or prior to the HASH( ) function appearing in the script. The password method is not suitable for use in computed fields because PASSWORD assignments are deleted when you close ACL. In addition, computed fields that use a password-based salt value are automatically removed from views when you reopen ACL – to avoid the calculation of hash values, using the default salt value, that differ from the original hash values.


Example Return value




The hash value for each number in the credit_card field











HASH(EXCLUDE("555-44-3322", "-"))











HASH("555443322", "my salt value 123")

HASH("555443322", 3)

(If PASSWORD 3 = my salt value 123)




(C) 2015 ACL Services Ltd. All Rights Reserved.