LEN

Calculates the number of characters in a string.

Note

  • This function is provided for SQL Server compatability.

  • For UTF-8 encoded TEXT strings, multi-byte characters are counted as a single character. To get the length in bytes, see OCTET_LENGTH. To get the length in characters, see CHAR_LENGTH.

Syntax

LEN( expr ) --> INT

Arguments

Parameter

Description

expr

String expression

Returns

Returns an integer containing the number of characters in the string.

Notes

  • If the value is NULL, the result is NULL.

Examples

For these examples, consider the following table and contents:

CREATE TABLE jabberwocky(line TEXT(50));

INSERT INTO jabberwocky VALUES
   ($$'Twas brillig, and the slithy toves$$), ('      Did gyre and gimble in the wabe:')
   ,('All mimsy were the borogoves,'), ('      And the mome raths outgrabe.')
   ,('"Beware the Jabberwock, my son!'), ('      The jaws that bite, the claws that catch!')
   ,('Beware the Jubjub bird, and shun'), ('      The frumious Bandersnatch!"');

CREATE TABLE alphabets(line TEXT(50));

INSERT INTO alphabets VALUES
   ('abcdefghijklmnopqrstuvwxyz'), ('กขฃคฅฆงจฉชซฌญฎฏฐฑฒณดตถทธนบปผฝพฟภมยรฤลฦวศษสหฬอฮฯ')
   , ('אבגדהוזחטיכלמנסעפצקרשת');

Length in characters and bytes of strings

ASCII characters take up 1 byte per character, while Thai takes up 3 bytes and Hebrew takes up 2 bytes.

t=> SELECT LEN(line), OCTET_LENGTH(line) FROM alphabets;
len | octet_length
----+-------------
 26 |           26
 47 |          141
 22 |           44

Length of an ASCII string

Note

SQream DB does not count trailing spaces, but does keep leading spaces.

t=> SELECT LEN('Trailing spaces are not counted            ');
len
---
 31

t=> SELECT LEN('            Leading spaces are counted');
len
---
 38

Absolute value on integer and floating point

numbers=> SELECT LEN(line) FROM jabberwocky;
len
---
 35
 38
 29
 34
 31
 47
 32
 33