Skip to main content

1E 23.11 (SaaS)

HASHSTRING

Function

HASHSTRING

Action

Produce a hash of a string value based on a named hashing algorithm

Parameters

(string) Hash algorithm; one of "MD5", "SHA1" or "SHA256"

(string) Input data

Return values

(string) Hash of input data using the named algorithm, expressed as a (lowercase) hexadecimal string

Example

// Show hash values using all three supported algorithms for two different sample data inputs, an empty string, and null
@values = SELECT "My sample data" AS Val UNION ALL SELECT "My other sample data" UNION ALL SELECT "" UNION ALL SELECT NULL;
@algos  = SELECT "MD5" AS Algo UNION ALL SELECT "SHA1" UNION ALL SELECT "SHA256";

SELECT  IFNULL(V.Val, "(null)") AS Input_Value
,       A.Algo AS Input_Algo
,       IFNULL(HASHSTRING(A.Algo, V.Val), "(null)") AS Output_Hash
FROM    @values as V
,       @algos AS A;

Notes

Note that HASHSTRING(algorithm, NULL) will always yield NULL, whereas HASHSTRING(algorithm, "") (i.e. hash of empty string) will produce a corresponding hash value.

Passing a non-string value, such as a number, as the input data will cause the input string to be converted to its default textual representation before being hashed, so HASHSTRING(algorithm, 123) will yield the same value as HASHSTRING(algorithm, "123").

You should, however, avoid this behaviour where possible and prefer to pass string data. This is because the implicit string conversion may yield an unexpected result, such as hashing constants e.g. true or false (which are represented internally as 0 and 1).

New in 8.1