类别：

字符串和二进制函数 （通用）

LENGTH、LEN

返回输入 字符串或二进制 值的长度。对于字符串，长度是字符数，UTF-8 字符计为单个字符。对于二进制，长度是字节数。

语法

LENGTH( <expression> )

LEN( <expression> )
Copy

实参

expression

输入表达式必须是字符串或二进制值。

返回

返回的数据类型是 INTEGER（更准确地说，是 NUMBER(18, 0)）。

排序规则详细信息

  • No impact. 在一个字符是一个字母以及一个字母就是一个字符的语言中，LENGTH 函数在有排序规则和没有排序规则的情况下，行为相同。

  • In languages where the alphabet contains digraphs or trigraphs (such as "Dz" and "Dzs" in Hungarian), each character in each digraph and trigraph is treated as an independent character, not as part of a single multi-character letter. 例如，尽管匈牙利语将“dz”视为单个字母，但 Snowflake 对 LENGTH(COLLATE('dz', 'hu')) 返回 2。

示例

创建表并插入 VARCHAR 值：

CREATE OR REPLACE TABLE length_function_demo (s VARCHAR);

INSERT INTO length_function_demo VALUES
  (''),
  ('Joyeux Noël'),
  ('Merry Christmas'),
  ('Veselé Vianoce'),
  ('Wesołych Świąt'),
  ('圣诞节快乐'),
  (NULL);
Copy

使用 LENGTH 函数查询表：

SELECT s, LENGTH(s) FROM length_function_demo;
Copy
+-----------------+-----------+
| S               | LENGTH(S) |
|-----------------+-----------|
|                 |         0 |
| Joyeux Noël     |        11 |
| Merry Christmas |        15 |
| Veselé Vianoce  |        14 |
| Wesołych Świąt  |        14 |
| 圣诞节快乐        |         5 |
| NULL            |      NULL |
+-----------------+-----------+

对于下一个示例，创建一个表并插入 BINARY 数据：

CREATE OR REPLACE TABLE binary_demo_table (
  v VARCHAR,
  b_hex BINARY,
  b_base64 BINARY,
  b_utf8 BINARY);

INSERT INTO binary_demo_table (v) VALUES ('hello');

UPDATE binary_demo_table SET
  b_hex    = TO_BINARY(HEX_ENCODE(v), 'HEX'),
  b_base64 = TO_BINARY(BASE64_ENCODE(v), 'BASE64'),
  b_utf8   = TO_BINARY(v, 'UTF-8');

SELECT * FROM binary_demo_table;
Copy
+-------+------------+------------+------------+
| V     | B_HEX      | B_BASE64   | B_UTF8     |
|-------+------------+------------+------------|
| hello | 68656C6C6F | 68656C6C6F | 68656C6C6F |
+-------+------------+------------+------------+

使用 LENGTH 函数查询表：

SELECT v, LENGTH(v),
       TO_VARCHAR(b_hex, 'HEX') AS b_hex, LENGTH(b_hex),
       TO_VARCHAR(b_base64, 'BASE64') AS b_base64, LENGTH(b_base64),
       TO_VARCHAR(b_utf8, 'UTF-8') AS b_utf8, LENGTH(b_utf8)
  FROM binary_demo_table;
Copy
+-------+-----------+------------+---------------+----------+------------------+--------+----------------+
| V     | LENGTH(V) | B_HEX      | LENGTH(B_HEX) | B_BASE64 | LENGTH(B_BASE64) | B_UTF8 | LENGTH(B_UTF8) |
|-------+-----------+------------+---------------+----------+------------------+--------+----------------|
| hello |         5 | 68656C6C6F |             5 | aGVsbG8= |                5 | hello  |              5 |
+-------+-----------+------------+---------------+----------+------------------+--------+----------------+
语言: 中文