Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT LENGTH('hello') AS letters;   -- 5

Try it

SQL Characters, numbers, blobs and NULL

Press Run (or Ctrl + Enter) to see the result here.

SQL Shortest and longest product names ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Counting a character with REPLACE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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()
SQL
-- 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. Use CHAR_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('') is 0 and LENGTH(NULL) is NULL. In Oracle an empty string is NULL, so its LENGTH('') is NULL.
  • Trailing spaces count in SQLite, PostgreSQL (for varchar), MySQL and Oracle; SQL Server's LEN ignores them.
  • Numbers are converted to text first in SQLite (LENGTH(12345) is 5); in other databases you may need CAST(n AS VARCHAR).
  • Counting occurrences: LENGTH(s) - LENGTH(REPLACE(s, ch, '')) gives the number of times a single character appears; divide by LENGTH(ch) for longer substrings.
  • Filtering on LENGTH(col) cannot use an ordinary index on col.