How to Generate Consistent MD5 Hashes in SFMC Using SQL and AMPscript

MD5 is a widely used hashing algorithm that produces a fixed-length 128-bit string, often used in Salesforce Marketing Cloud for things like subscriber keys and consistent identifiers. While MD5 isn’t suitable for strong cryptographic security, you can reliably generate it in both SQL queries and AMPscript by standardizing the input — trimming spaces, converting to lowercase, and handling character encoding like UTF-16 when hashing across platforms. In this guide we’ll walk through how to implement MD5 hashing in SFMC SQL and AMPscript so you get consistent results every time.

Generate MD5 hash in MSSQL

To generate MD5 hash with MSSQL we can use following SQL select clause

CONVERT(VARCHAR(32), HashBytes('MD5', LTRIM(RTRIM(Lower([EmailAddress])))), 2) as SubscriberKey

1. HashBytes('MD5', LTRIM(RTRIM(Lower([EmailAddress])))):
   • Lower([EmailAddress]): This function converts the value in the [EmailAddress] field to lowercase. It ensures that the hash calculation is case-insensitive.
   • LTRIM(RTRIM()): These functions are used to remove leading and trailing spaces from the lowercase email address. Trimming is commonly done to ensure consistent results when hashing.
   • HashBytes('MD5', ...): This function calculates the MD5 hash of the processed email address, producing a binary output.
2. CONVERT(VARCHAR(32), ..., 2) as SubscriberKey:
   • CONVERT(VARCHAR(32), ..., 2): This part converts the binary MD5 hash result into a hexadecimal string representation of length 32 characters.
   • as SubscriberKey: This gives the resulting hexadecimal string the alias “SubscriberKey” for use in the query’s output.
   • Last argument of VARCHAR is the conversion style. 2 = hex output without 0x. 1 = Hex output with 0x

In MSSQL Server, NVARCHAR and NCHAR data types store Unicode characters, and by default, they use UTF-16 encoding. So, when you convert binary data (like an MD5 hash) to a VARCHAR in SQL Server without specifying a character encoding, it typically uses the database’s default encoding, which is UTF-16 for Unicode data types.

SQL Server HASHBYTES Function

The HASHBYTES function in SQL Server computes a fixed-length binary hash of an input using a specified hashing algorithm. The basic syntax is:

HASHBYTES('<algorithm>', { @input | 'input' })

The first argument, <algorithm>, specifies which hashing algorithm to use and must be provided as a quoted string. Supported values include MD2, MD4, MD5, SHA, SHA1, SHA2_256, and SHA2_512. Starting with SQL Server 2016 (Should match SFMC MSSQL version), all algorithms except SHA2_256 and SHA2_512 are officially deprecated, though they continue to work.

For the second argument, you can pass either a variable (@input) or a direct expression ('input') that evaluates to a character or binary string. Valid input types are varchar, nvarchar, or varbinary.

The return value is a varbinary hash whose length depends on the algorithm chosen: 16 bytes (128 bits) for MD2/MD4/MD5, 20 bytes (160 bits) for SHA/SHA1, 32 bytes (256 bits) for SHA2_256, and 64 bytes (512 bits) for SHA2_512.

Choosing the Correct VARCHAR Length for Hash Output

Each hashing algorithm produces a fixed-size binary output. When you convert that binary hash into a hexadecimal string, the required VARCHAR length depends on the algorithm you use. If the length is too short, the hash will be silently truncated and comparisons will fail.

Use the following lengths when converting hashes to hexadecimal strings:

VARCHAR(32)  -- MD5 (128-bit / 16-byte hash)
VARCHAR(40)  -- SHA1 (160-bit / 20-byte hash)
VARCHAR(64)  -- SHA2_256 (256-bit / 32-byte hash)

Each byte is represented by two hexadecimal characters, which is why the string length is always double the byte length of the hash.

Always explicitly define the VARCHAR length when using CONVERT, especially in Salesforce Marketing Cloud and other marketing platforms, to avoid inconsistent or truncated hash values.

Hashing Numeric Values

he HASHBYTES function cannot hash numeric data types directly. If you need to generate an MD5 hash from a number (for example an internal ID, customer number, or account ID), the value must first be converted to a string or binary format.

This step is critical because hashing operates on bytes, not numeric representations. Different implicit conversions or formats will produce different hashes.

CONVERT(
  VARCHAR(32),
  HASHBYTES(
    'MD5',
    CONVERT(VARCHAR(50), YourNumericField)
  ),
  2
)

Generate MD5 hash in AMPscript

If you intend to hash strings in SSJS or AMPScript to match the results obtained from SQL Server (which uses UTF-16 encoding by default), you should ensure that your string encoding matches. This means that you should normalize and encode your strings in UTF-16 before hashing to achieve consistent results.

Thus in AMPScript and SSJS we will have to use UTF-16 character set for MD5 hashing.

%%[
/*
MD5(1,2)
1 - string to hash
3 - character set. Default is UTF-8
*/
SET @emailAddress = "exmample@martechnotes.com"
SET @MD5EmailAddress  = MD5(@emailAddress, "UTF-16")
]%%

<script runat=server>
  /*
  MD5(1,2)
  1 - string to hash
  3 - character set. Default is UTF-8
  */
   var email = Platform.Variable.GetValue("@emailAddress");
   Platform.Function.MD5(email, 'UTF-16');
</script>

Share With Others