SQL Reference ยท Scalar Functions
SQL LENGTH() function
Reference for the SQL string length functions: LENGTH, CHAR_LENGTH, LEN and octet length, characters versus bytes, NULL handling and trailing spaces.
LENGTH(s) returns how long a string is. It is used to validate input (a phone number must have 10 digits), to find the longest or shortest values, to compute positions together with SUBSTR(), and to count occurrences of a character with a small trick.
The tricky part is what "long" means: characters or bytes. The SQL standard calls the character count CHAR_LENGTH() and the byte count OCTET_LENGTH(). Databases then add their own LENGTH or LEN that may mean either.
SELECT LENGTH('hello') AS letters; -- 5
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
LENGTH(string) CHAR_LENGTH(string) OCTET_LENGTH(string)
| Name | Description |
|---|---|
string |
A text value (a number is converted to text; a blob is measured in bytes in SQLite). |
Returns: an integer. NULL if the input is NULL.
Dialect differences
| Database | Characters | Bytes |
|---|---|---|
| Standard SQL | CHAR_LENGTH(), CHARACTER_LENGTH() |
OCTET_LENGTH() |
| SQLite | LENGTH(text) |
LENGTH(blob), CAST(s AS BLOB) |
| PostgreSQL | LENGTH(), CHAR_LENGTH() |
OCTET_LENGTH() |
| MySQL | CHAR_LENGTH() |
LENGTH() returns bytes |
| SQL Server | LEN() (ignores trailing spaces) |
DATALENGTH() |
| Oracle | LENGTH() |
LENGTHB() |
-- MySQL: 'รฉ' is 2 bytes in UTF-8 SELECT LENGTH('รฉ'), CHAR_LENGTH('รฉ'); -- 2, 1 -- SQL Server: LEN drops trailing spaces SELECT LEN('ab '), DATALENGTH('ab '); -- 2, 4
Usage notes
- MySQL's
LENGTH()counts bytes, which gives wrong results for non-ASCII text. UseCHAR_LENGTH()there. - SQLite counts code points, not what a reader sees as characters. An emoji made of several code points reports more than 1.
- Empty string versus NULL.
LENGTH('')is0andLENGTH(NULL)isNULL. In Oracle an empty string isNULL, so itsLENGTH('')isNULL. - Trailing spaces count in SQLite, PostgreSQL (for
varchar), MySQL and Oracle; SQL Server'sLENignores them. - Numbers are converted to text first in SQLite (
LENGTH(12345)is 5); in other databases you may needCAST(n AS VARCHAR). - Counting occurrences:
LENGTH(s) - LENGTH(REPLACE(s, ch, ''))gives the number of times a single character appears; divide byLENGTH(ch)for longer substrings. - Filtering on
LENGTH(col)cannot use an ordinary index oncol.
Related
SUBSTR(),TRIM(),REPLACE(),INSTR()- Lessons: String functions, Types and CAST