1E 23.11 (SaaS)

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

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

(string) Input data

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

// 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;

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