使用二进制数据

BINARY 数据类型的实用性和灵活性最好能通过示例进行证明。本主题提供了涉及 BINARY 数据类型及其三种编码方案的任务的实际示例。

本主题内容：

在十六进制和 Base64 之间转换

在十六进制和 Base64 字符串之间进行转换时，可以将 BINARY 数据类型用作中间步骤。

使用 TO_CHAR、TO_VARCHAR 从十六进制转换为 Base64：

SELECT c1, to_char(to_binary(c1, 'hex'), 'base64') FROM hex_strings;

+----------------------+-----------------------------------------+
| C1                   | TO_CHAR(TO_BINARY(C1, 'HEX'), 'BASE64') |
|----------------------+-----------------------------------------|
| df32ede209ed5a4e3c25 | 3zLt4gntWk48JQ==                        |
| AB4F3C421B           | q088Qhs=                                |
| 9324df2ecc54         | kyTfLsxU                                |
+----------------------+-----------------------------------------+
Copy

从 Base64 转换为十六进制：

SELECT c1, to_char(to_binary(c1, 'base64'), 'hex') FROM base64_strings;

+------------------+-----------------------------------------+
| C1               | TO_CHAR(TO_BINARY(C1, 'BASE64'), 'HEX') |
|------------------+-----------------------------------------|
| 3zLt4gntWk48JQ== | DF32EDE209ED5A4E3C25                    |
| q088Qhs=         | AB4F3C421B                              |
| kyTfLsxU         | 9324DF2ECC54                            |
+------------------+-----------------------------------------+
Copy

在文本和 UTF-8 字节之间转换

Snowflake 中的字符串由 Unicode 字符组成，而二进制值由字节组成。通过将字符串转换为 UTF-8 格式的二进制值，我们可以直接对组成 Unicode 字符的字节进行操作。

使用 TO_BINARY 将包含单个字符的字符串转换为以字节表示的 UTF-8 格式：

SELECT c1, to_binary(c1, 'utf-8') FROM characters;

+----+------------------------+
| C1 | TO_BINARY(C1, 'UTF-8') |
|----+------------------------|
| a  | 61                     |
| é  | C3A9                   |
| ❄  | E29D84                 |
| π  | CF80                   |
+----+------------------------+
Copy

使用 TO_CHAR、TO_VARCHAR 将一个 UTF-8 字节序列转换为字符串：

SELECT to_char(X'41424320E29D84', 'utf-8');

+-------------------------------------+
| TO_CHAR(X'41424320E29D84', 'UTF-8') |
|-------------------------------------|
| ABC ❄                               |
+-------------------------------------+
Copy

获取 Base64 格式的 MD5 摘要

使用 TO_CHAR、TO_VARCHAR 将二进制的 MD5 摘要转换为 Base64 字符串：

SELECT to_char(md5_binary(c1), 'base64') FROM variants;

+----------+-----------------------------------+
| C1       | TO_CHAR(MD5_BINARY(C1), 'BASE64') |
|----------+-----------------------------------|
| 3        | 7MvIfktc4v4oMI/Z8qe68w==          |
| 45       | bINJzHJgrmLjsTloMag5jw==          |
| "abcdef" | 6AtQFwmJUPxYqtg8jBSXjg==          |
| "côté"   | H6G3w1nEJsUY4Do1BFp2tw==          |
+----------+-----------------------------------+
Copy

使用可变格式转换为二进制

使用从字符串中提取的二进制格式将字符串转换为二进制值。该语句包括 TRY_TO_BINARY 和 SPLIT_PART 函数：

SELECT c1, try_to_binary(split_part(c1, ':', 2), split_part(c1, ':', 1)) AS binary_value FROM strings;

+-------------------------+----------------------+
| C1                      | BINARY_VALUE         |
|-------------------------+----------------------|
| hex:AB4F3C421B          | AB4F3C421B           |
| base64:c25vd2ZsYWtlCg== | 736E6F77666C616B650A |
| utf8:côté               | 63C3B474C3A9         |
| ???:abc                 | NULL                 |
+-------------------------+----------------------+
Copy

尝试多种格式的转换：

SELECT c1, coalesce(
  x'00' || try_to_binary(c1, 'hex'),
  x'01' || try_to_binary(c1, 'base64'),
  x'02' || try_to_binary(c1, 'utf-8')) AS binary_value FROM strings;

+------------------+------------------------+
| C1               | BINARY_VALUE           |
|------------------+------------------------|
| ab4f3c421b       | 00AB4F3C421B           |
| c25vd2ZsYWtlCg== | 01736E6F77666C616B650A |
| côté             | 0263C3B474C3A9         |
| 1100             | 001100                 |
+------------------+------------------------+
Copy

备注

由于上述查询使用了 TRY_TO_BINARY，因此，如果无法识别格式或无法使用给定格式解析字符串，结果将是 NULL。

使用 SUBSTR、SUBSTRING 和 DECODE 将上一个示例的结果转换回字符串：

SELECT c1, to_char(
  substr(c1, 2),
  decode(substr(c1, 1, 1), x'00', 'hex', x'01', 'base64', x'02', 'utf-8')) AS string_value
  FROM bin;

+------------------------+------------------+
| C1                     | STRING_VALUE     |
|------------------------+------------------|
| 00AB4F3C421B           | AB4F3C421B       |
| 01736E6F77666C616B650A | c25vd2ZsYWtlCg== |
| 0263C3B474C3A9         | côté             |
| 001100                 | 1100             |
+------------------------+------------------+
Copy

使用 JavaScript UDF 自定义解码

BINARY 数据类型允许存储任意数据。由于 JavaScript UDFs 通过 Uint8Array 支持该数据类型（参见 JavaScript UDFs 简介），因此可以在 JavaScript 中实现自定义解码逻辑。这不是最有效的工作方式，但却非常强大。

创建基于第一个字节进行解码的函数：

CREATE FUNCTION my_decoder (B binary) RETURNS variant LANGUAGE javascript AS '
  if (B[0] == 0) {
      var number = 0;
      for (var i = 1; i < B.length; i++) {
          number = number * 256 + B[i];
      }
      return number;
  }
  if (B[0] == 1) {
      var str = "";
      for (var i = 1; i < B.length; i++) {
          str += String.fromCharCode(B[i]);
      }
      return str;
  }
  return null;';
Copy
SELECT c1, my_decoder(c1) FROM bin;

+----------------+----------------+
| C1             | MY_DECODER(C1) |
|----------------+----------------|
| 002A           | 42             |
| 0148656C6C6F21 | "Hello!"       |
| 00FFFF         | 65535          |
| 020B1701       | null           |
+----------------+----------------+
Copy
语言: 中文