function md5()

Using MD5 algorithm in T-SQL

In T-SQL, we can use the HASHBYTES function to encrypt to MD5 for data up to 8000 characters. For longer data or binary data we can use the system function fn_repl_hash_binary.

Syntax

HashBytes('MD5', @hashThis)

Basic example:

SELECT HashBytes('MD5', 'Hello world!')

The above example will output:

0x86FB269D190D2C85F6E0468CECA42A20

As you can see, the result has 34 characters (prefixed with 0x) and is in upper case. In the next example, we will standardize the output.

Example #2 standardized output

SELECT LOWER(CONVERT(VARCHAR(32),HashBytes('MD5', 'Hello world!'),2))

Example #2 will output:

86fb269d190d2c85f6e0468ceca42a20

Example #3 with binary data

SELECT LOWER(CONVERT(VARCHAR(32),master.sys.fn_repl_hash_binary(CAST ('Hello world!' AS varbinary)),2));

Example #3 will output:

86fb269d190d2c85f6e0468ceca42a20

Example #4 with salt before string to be encrypted:

In some cases, it is worth using a salted MD5 hash for added security. This means that you add the string "salt" before or after the string to be encrypted.

DECLARE @salt VARCHAR(32) = N'i#8^*uu'
DECLARE @str2hash VARCHAR(100) = N'Hello world!'

SELECT LOWER(CONVERT(VARCHAR(32),HashBytes('MD5', CONCAT(@salt, @str2hash)),2))

Example #4 will output:

5e5eb686808a7c39710dccf1ce03a4e6

External resource about T-SQL hashing:

docs.microsoft.com