On this page
Syntax
LENGTH(string)CHAR_LENGTH(string)CHARACTER_LENGTH(string)LEN(string)DATALENGTH(expression) | Parameter | Type | Required | Description |
|---|---|---|---|
string |
character string | yes | The value to measure. A NULL input produces a NULL result on every engine. |
expression |
any value | yes | For SQL Server DATALENGTH, any expression whose storage size in bytes is returned. |
How it works
LENGTH() looks like a simple string function, and it is one of the most common alongside SUBSTRING() and TRIM. The catch is that the same name behaves differently across engines, which is one of the most frequent portability surprises in SQL. You can browse the full set on the SQL Functions Library.
The key split is bytes versus characters. In MySQL, LENGTH() returns the number of bytes, while CHAR_LENGTH() (also spelled CHARACTER_LENGTH()) returns the number of characters. For plain ASCII text the two agree, because each character is one byte. For UTF8 text they diverge, because an accented letter or a non Latin letter can occupy two, three, or four bytes. In PostgreSQL and SQLite, LENGTH() already counts characters, so it matches what a human would count.
SQL Server has no LENGTH() function at all. It offers LEN(), which returns the character count but ignores trailing spaces, and DATALENGTH(), which returns the number of bytes used for storage. Because these functions measure text in different units, the storage width you pick matters too. See SQL data types explained for how CHAR, VARCHAR, and NVARCHAR affect byte counts.
Examples
Count the characters in a column
SELECT name, LENGTH(name) AS chars
FROM products
ORDER BY name;name | chars =======+====== Gadget | 6 Gizmo | 5 Widget | 6
Bytes versus characters on a multibyte string (MySQL)
SELECT LENGTH(city) AS byte_count,
CHAR_LENGTH(city) AS char_count
FROM places
WHERE id = 1;byte_count | char_count
===========+===========
5 | 4LEN and DATALENGTH in SQL Server
SELECT LEN('SQL Server ') AS len_value,
DATALENGTH('SQL Server ') AS datalength_value;len_value | datalength_value
==========+=================
10 | 13Use CHAR_LENGTH for a correct character count
SELECT product_code, CHAR_LENGTH(product_code) AS chars
FROM catalog
WHERE CHAR_LENGTH(product_code) <> 8;product_code | chars =============+====== AB12 | 4 XY7799001 | 9
Measure a value after trimming it
SELECT LENGTH(TRIM(code)) AS trimmed_length
FROM tokens
WHERE id = 7;trimmed_length
==============
11Common mistakes
SELECT LENGTH(city) AS chars
FROM places;SELECT CHAR_LENGTH(city) AS chars
FROM places;The classic MySQL trap: LENGTH() returns bytes, so a UTF8 column that stores accented or non Latin letters reports more than the real character count. Use CHAR_LENGTH() (or CHARACTER_LENGTH()) whenever you want to count characters.
SELECT LEN('data ') AS n; /* returns 4 */SELECT DATALENGTH('data ') AS n; /* returns 7 */In SQL Server, LEN() silently strips trailing spaces before counting, which hides padding in fixed width CHAR columns. Use DATALENGTH() when trailing spaces matter or when you need the true storage size in bytes.
SELECT LENGTH(name) FROM customers;SELECT LEN(name) FROM customers;SQL Server has no LENGTH() function, so this query fails there. Use LEN() for a character count and DATALENGTH() for a byte count when writing for SQL Server.
Performance
Wrapping a column in LENGTH() inside a WHERE clause, such as WHERE LENGTH(name) > 20, makes the predicate non sargable: the engine must compute the length for every row, so a plain index on the column cannot be used. If you filter on length often, store the length in its own column or build a functional index where your engine supports one.
The unit you measure in has a cost too. On a UTF8 column, CHAR_LENGTH() may need to walk the bytes to count code points, whereas a byte count is a direct read of stored size. For very large text scans this difference can add up, so measure characters only when you truly need characters.
When you only need to know whether a string is empty, prefer a direct comparison such as col = '' over LENGTH(col) = 0. The comparison can use an index, and it avoids a function call on every row.
Interview questions
In MySQL, what is the difference between LENGTH and CHAR_LENGTH?
LENGTH() returns the number of bytes, while CHAR_LENGTH() (also written CHARACTER_LENGTH()) returns the number of characters. They agree for ASCII text but differ for UTF8 text, because a single multibyte character occupies more than one byte.
SELECT LENGTH(word) AS bytes,
CHAR_LENGTH(word) AS chars
FROM dictionary;How do you measure the length of a string in SQL Server?
SQL Server has no LENGTH() function. Use LEN() for the character count, remembering that it ignores trailing spaces, and DATALENGTH() for the number of bytes used to store the value.
Does LENGTH count trailing spaces?
In MySQL, PostgreSQL, and SQLite, LENGTH() counts trailing spaces like any other character. In SQL Server, LEN() does not count trailing spaces, but DATALENGTH() does.
What does LENGTH return in PostgreSQL and SQLite?
It returns the number of characters, not bytes. This matches what a person would count, so on those engines you do not need a separate character function for normal text. PostgreSQL also offers octet_length() when you specifically want bytes.
How would you count characters correctly for a UTF8 column across dialects?
Use CHAR_LENGTH() in MySQL and PostgreSQL, LENGTH() in SQLite, and LEN() in SQL Server. Avoid MySQL LENGTH() for this, because it returns bytes and overcounts multibyte characters.