Categories:

String & binary functions (General)

LENGTH, LEN

Returns the length of an input string or binary value. For strings, the length is the number of characters, and UTF-8 characters are counted as a single character. For binary, the length is the number of bytes.

Syntax

LENGTH( <expression> )
LEN( <expression> )
Copy

Arguments

expression

The input expression must be a string or binary value.

Returns

The returned data type is INTEGER (more precisely, NUMBER(18, 0)).

Collation details

  • No impact. In languages in which one character is one letter and vice versa, LENGTH behaves the same with and without collation.

  • 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.

    • In languages where a pair or triplet of characters (e.g. “dz”) is treated as a single letter of the alphabet, Snowflake still measures length in characters, not letters. For example, although Hungarian treats “dz” as a single letter, Snowflake returns 2 for LENGTH(COLLATE('dz', 'hu')).

Examples

SELECT s, LENGTH (s) FROM strings;

----------------------+-----------+
          s           | length(s) |
----------------------+-----------+
                      | 0         |
 Joyeux Noël          | 11        |
 Merry Christmas      | 15        |
 Veselé Vianoce       | 14        |
 Wesołych Świąt       | 14        |
 圣诞节快乐             | 5         |
 [NULL]               | [NULL]    |
----------------------+-----------+
Copy

This example uses BINARY data:

Create and fill a table:

CREATE TABLE binary_table (v VARCHAR, 
  b_hex BINARY, b_base64 BINARY, b_utf8 BINARY);
INSERT INTO binary_table (v) VALUES ('hello');
UPDATE binary_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')
  ;
Copy

Output:

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_table;
+-------+-----------+------------+---------------+----------+------------------+--------+----------------+
| 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 |
+-------+-----------+------------+---------------+----------+------------------+--------+----------------+
Copy
Language: English