HEX Function - IBM DB2 to SQL Server Migration

In DB2 the HEX function converts a string to its hexadecimal representation. In SQL Server you can use CONVERT expressions with appropriate styles.

DB2:

  -- Get hexadecimal representation of 'abc'
  SELECT HEX('abc') FROM sysibm.sysdummy1;
  # 616263

SQL Server:

  -- Get hexadecimal representation of 'abc' using style 2 of CONVERT function
  SELECT CONVERT(VARCHAR, CONVERT(VARBINARY, 'abc'), 2);
  # 616263

Note that if you use style 1 in SQL Server, hexadecimal string contains 0x and style 0 (or absence of style parameter) converts the string back to its ASCII form:

SQL Server:

  -- With style 1 the hexadecimal string includes 0x
  SELECT CONVERT(VARCHAR, CONVERT(VARBINARY, 'abc'), 1);
  # 0x616263
 
  -- If style is 0 or absent the string is converted back to 'abc'
  SELECT CONVERT(VARCHAR, CONVERT(VARBINARY, 'abc'), 0);
  # abc

For more information, see IBM DB2 to SQL Server Migration.