在Azure SQL数据仓库中将二进制列转换为字符串

Question

我目前在Postgres和Redshift中有一些函数，这些函数接受随机生成的字符串，对其进行散列，然后使用一部分哈希来生成0-99之间的随机数。我试图在Azure SQL数据仓库中复制此功能，以便在SQL中获得与Postgres和Redshift相同的值。

我遇到的问题是，当我将结果转换到VARCHAR或使用string函数时，结果是一个大不相同的字符串。我想将md5函数的结果作为一个相同的VARCHAR。

为了说明，下面是Azure SQL DW中的一个查询：

SELECT
  'abc123' as random_string,
  HASHBYTES('md5', 'abc123') as md5,
  CAST(HASHBYTES('md5', 'abc123') AS VARCHAR) as md5_varchar,
  RIGHT(HASHBYTES('md5', 'abc123'), 5) as md5_right
;

这会产生

random_string,md5,md5_varchar
abc123,0xE99A18C428CB38D5F260853678922E03,éšÄ(Ë8Õò`…6x’.,6x’.

如您所见，产生的varchar与md5函数的输出有很大不同。是否有方法将md5的结果转换为相同的字符串？

在Postgres和Redshift中，md5函数的结果是一个VARCHAR，因此对其进行转换很简单。

以下是Redshift和Postgres中的查询：

-- Redshift
SELECT
  'abc123' as random_string,
  right(strtol(right(md5('abc123'), 3), 16), 2)::INT as tranche
;

-- Postgres
SELECT
  'abc123' as random_string,
  right(('x' || lpad(right(md5('abc123'), 3), 4, '0')) :: BIT(16) :: INT :: VARCHAR, 2) :: INT AS tranche
;

这两个函数都返回值87。

Answer 1

使用转换应该可以解决这个问题：

CONVERT(VARCHAR(32),HashBytes('MD5', 'abc123'),2)

这是因为您可以为样式定义参数，这是在我们转换varbinary时需要的。在这里描述：https://technet.microsoft.com/pl-pl/library/ms187928(v=sql.105).aspx

以下是文档中关于用转换进行二进制转换的备注部分：

当表达式为二进制(N)、var二进制(N)、char(n)或varchar(n)时，样式可以是下表所示的值之一。未在表中列出的样式值返回错误。 0(违约) 将ASCII字符转换为二进制字节或二进制字节到ASCII字符。每个字符或字节转换为1:1。如果data_type是二进制类型，则将字符0x添加到结果的左边。 1，2 如果data_type是二进制类型，则表达式必须是字符表达式。表达式必须由偶数十六进制数(0，1，2，3，4，5，6，7，8，9，a，B，c，d，e，F，A，b，C，D，E，f)组成。如果样式设置为1，则0x字符必须是表达式中的前两个字符。如果表达式包含奇数字符，或者其中任何字符无效，则会引发错误。如果转换表达式的长度大于data_type的长度，则结果将被正确截断。固定长度的data_types是较大的，然后转换的结果将有零添加到结果的右边。如果data_type是字符类型，则表达式必须是二进制表达式。每个二进制字符被转换成两个十六进制字符。如果转换后的表达式的长度大于data_type长度，则将正确截断。如果data_type是一个固定大小的字符类型，且转换结果的长度小于其data_type长度，则在转换表达式的右侧添加空格以保持偶数十六进制数字。0x字符将添加到样式1的转换结果的左侧。