LENGTH() Function in SQL
Introduction
LENGTH is a string function used to measure a text value. A simple call such as LENGTH('Hello') produces 5 in databases where the function measures the five characters or bytes used by this ASCII value. That basic result is easy to understand, but production use requires a more precise question: what unit is being measured?
SELECT LENGTH('Hello') AS measured_length;
-- Typical result for ASCII text: 5
Different database systems use different names and semantics. MySQL LENGTH returns bytes, PostgreSQL LENGTH on text returns characters, Oracle LENGTH uses character semantics, and SQL Server normally uses LEN rather than LENGTH. Spaces, trailing padding, NULL values, empty strings, Unicode encoding, and column data types can also affect the result.
This means LENGTH is not merely a counting convenience. It is a useful validation, profiling, cleanup, reporting, and diagnostic tool, provided that the query defines the required unit and accounts for the target DBMS. The safest mental model is to decide whether the rule concerns people-visible characters, encoded bytes, or storage size before selecting a function.
Basic LENGTH Syntax
The common form accepts one string expression. That expression may be a literal, a column, a function result, or a calculated value that the database can interpret as character data.
LENGTH(string_expression)
The function returns a numeric result when the expression has a known value. When the input is NULL, the result is normally NULL because the database cannot determine the length of an unknown value.
SELECT LENGTH('SQL') AS text_length;
-- Typical result: 3
Measuring a String Literal
A literal is the clearest way to learn the function. The word Database contains eight ASCII characters, so character and byte semantics produce the same result in common encodings.
SELECT LENGTH('Database') AS text_length;
-- Typical result: 8
ASCII examples are useful for syntax but can hide portability issues. The same function may behave differently when a value contains accented letters, non-Latin scripts, emoji, combining marks, or database-specific fixed-width padding.
Using LENGTH with a Column
When LENGTH receives a column, it is evaluated separately for every selected row. A query can show each employee name beside its measured length, making unusually short or long values easy to inspect.
SELECT employee_id,
employee_name,
LENGTH(employee_name) AS name_length
FROM employees;
If the names are John, Michael, and Christopher, ordinary character semantics return 4, 7, and 11. A NULL employee name remains NULL unless the query explicitly substitutes another value.
Spaces Contribute to Length
Spaces are data. The value Hello SQL contains five letters, one internal space, and three more letters, so its measured ASCII length is 9. LENGTH does not remove or ignore internal whitespace.
SELECT LENGTH('Hello SQL') AS text_length;
-- Typical result: 9
This behavior is important when reviewing names, labels, addresses, imported files, and manually entered values. Counting only letters by eye can produce a different answer from the database because tabs, line breaks, non-breaking spaces, and other whitespace characters may also be present.
Leading and Trailing Spaces
Leading spaces are commonly included. For example, two spaces followed by SQL produce a length of 5 under ordinary ASCII byte or character semantics.
SELECT LENGTH(' SQL') AS leading_space_length;
Trailing-space behavior needs more care. MySQL LENGTH and PostgreSQL LENGTH on a literal such as 'SQL ' count both trailing spaces. SQL Server LEN excludes trailing spaces, while DATALENGTH measures encoded storage bytes. Fixed-width CHAR values can introduce additional padding rules. Never generalize a result from one function, type, or database to every platform.
Ignoring Surrounding Spaces with TRIM
When surrounding whitespace is not meaningful, clean the value before measuring it. TRIM removes supported leading and trailing spaces, and LENGTH then evaluates the cleaned result.
SELECT LENGTH(TRIM(' SQL ')) AS trimmed_length;
-- Typical result: 3
TRIM should reflect a requirement rather than be added automatically. Spaces may be invalid in a username but intentional in fixed-format content. Also confirm which characters the DBMS treats as trim characters; a tab or non-breaking space may not be removed by a default TRIM call.
Comparing Length Before and After TRIM
A difference between the original and trimmed lengths reveals surrounding whitespace. This is a practical data-quality check because it identifies affected rows without changing them.
SELECT customer_id,
name,
LENGTH(name) AS original_length,
LENGTH(TRIM(name)) AS trimmed_length
FROM customers
WHERE LENGTH(name) <> LENGTH(TRIM(name));
If a value is ' John ', ordinary character semantics report 6 before trimming and 4 afterward. The query is useful during imports and cleanup reviews, but remediation should still consider whether the surrounding characters are ordinary spaces and whether changing the source value is safe.
Empty Strings
In databases that distinguish a zero-length string from NULL, LENGTH of an empty string returns 0. The value exists but contains no characters.
SELECT LENGTH('') AS empty_string_length;
-- Typical result outside Oracle: 0
Oracle SQL is an important exception because it treats a zero-length character string as NULL. Consequently, code that distinguishes missing text from present-but-empty text on another platform may need redesign during an Oracle migration.
NULL Input
NULL represents a missing or unknown value, not a string with zero characters. Most length functions therefore propagate NULL.
SELECT LENGTH(NULL) AS unknown_length;
-- Typical result: NULL
This distinction matters in filters and aggregates. A condition such as LENGTH(description) = 0 does not select NULL descriptions because comparisons with NULL are unknown. Test NULL explicitly with IS NULL when missing values form a separate business category.
Handling NULL with COALESCE
COALESCE can replace NULL before or after measurement. Both patterns can produce zero when missing text should be treated as empty, although the exact empty-string behavior remains database dependent.
SELECT LENGTH(COALESCE(description, '')) AS normalized_length
FROM products;
SELECT COALESCE(LENGTH(description), 0) AS normalized_length
FROM products;
Do this only when the requirement says missing and empty text are equivalent. Converting NULL to zero for convenience can hide a data-quality problem and erase information that downstream reporting needs.
NULL Versus Empty Text
The conceptual difference is straightforward: NULL means the value is not known, while an empty string means the value is known to contain zero characters. A profile should often report these states separately.
SELECT
SUM(CASE WHEN notes IS NULL THEN 1 ELSE 0 END) AS missing_notes,
SUM(CASE WHEN notes IS NOT NULL AND LENGTH(notes) = 0 THEN 1 ELSE 0 END) AS empty_notes
FROM customer_contacts;
Separating these counts helps teams distinguish incomplete collection from deliberate blank content. On Oracle, an empty character string collapses into NULL, so this exact distinction is not available in the same way.
Length Validation in WHERE
LENGTH can support ad hoc validation. For example, a review may identify usernames outside an expected range.
SELECT user_id, username
FROM users
WHERE LENGTH(username) BETWEEN 5 AND 20;
This query selects values whose measured length falls within the range. It does not prove that the username has valid characters, permitted normalization, or a unique value. Length is one rule in a larger validation policy.
Finding Values That Are Too Short
Suspiciously short values can reveal truncated imports, placeholder values, or invalid source data. The threshold should come from a real business rule rather than an arbitrary guess.
SELECT customer_id, customer_name
FROM customers
WHERE LENGTH(TRIM(customer_name)) < 3;
Trimming before measurement is appropriate here only if surrounding spaces are never part of a valid customer name. The query should also decide whether NULL names belong in the result.
Finding Values That Are Too Long
Long text can exceed downstream field limits, report layouts, API contracts, or UI constraints. A profiling query can locate descriptions over a selected threshold.
SELECT product_id,
LENGTH(description) AS description_length
FROM products
WHERE LENGTH(description) > 500;
A measured character limit is not the same as a byte or packet limit. If an external interface limits UTF-8 bytes rather than characters, use the DBMS function that reports bytes and test the final encoded payload.
Checking an Exact Length
Exact-length checks are common for controlled identifiers such as legacy product codes. To find violations, invert the expected condition.
SELECT product_id, product_code
FROM products
WHERE product_code IS NULL
OR LENGTH(product_code) <> 8;
Including the explicit NULL branch is important because LENGTH(NULL) <> 8 is unknown rather than true. The query still needs a format rule if the code must contain only particular letters or digits.
Length Ranges with AND and BETWEEN
An inclusive range can be written with two comparisons or BETWEEN. These expressions communicate the same basic rule.
WHERE LENGTH(username) >= 5
AND LENGTH(username) <= 20
WHERE LENGTH(username) BETWEEN 5 AND 20
Choose the form that makes boundary behavior clearest to reviewers. For exclusive limits, write explicit greater-than and less-than operators rather than forcing BETWEEN into a different meaning.
Password Length Requires Application Context
Password policies usually validate the user-entered password before secure hashing. A database should store a password hash produced by an appropriate password-hashing algorithm, not plaintext that can be measured later.
The length of a stored hash reflects the hash representation, not the original password. Queries such as LENGTH(password_hash) can verify storage format consistency, but they cannot enforce the original password's minimum character count.
Phone and Email Validation Limits
A phone number with ten measured characters is not necessarily a valid phone number, and international numbers do not all share one length. Likewise, an email address longer than five characters is not necessarily syntactically or operationally valid.
SELECT customer_id, clean_phone
FROM customers
WHERE clean_phone IS NULL
OR LENGTH(clean_phone) <> 10;
Use length as one signal alongside allowed-character checks, format validation, country rules, domain requirements, and business constraints. Avoid presenting a length-only query as complete validation.
Nesting LENGTH with CONCAT
Functions can be nested. CONCAT first constructs a display value, and LENGTH then measures the result.
SELECT employee_id,
LENGTH(CONCAT(first_name, ' ', last_name)) AS full_name_length
FROM employees;
The result inherits CONCAT's NULL and type-conversion behavior. If the target DBMS propagates a NULL component, the final length may also be NULL. If CONCAT treats NULL as empty, the result can instead measure a partial name.
Nesting LENGTH with UPPER and LOWER
For ordinary ASCII letters, changing case does not change the number of characters or bytes. That observation is not a universal Unicode rule.
SELECT employee_name,
LENGTH(UPPER(employee_name)) AS upper_length,
LENGTH(LOWER(employee_name)) AS lower_length
FROM employees;
Some Unicode case mappings can expand, contract, or otherwise transform text according to language and collation rules. If a limit applies after normalization or case conversion, measure the transformed output rather than assuming its size is unchanged.
Nesting LENGTH with REPLACE
REPLACE can remove selected characters before measurement. This example measures a name after ordinary spaces are removed.
SELECT customer_id,
LENGTH(REPLACE(name, ' ', '')) AS length_without_spaces
FROM customers;
This removes only the exact character supplied to REPLACE. Tabs, line breaks, non-breaking spaces, and other Unicode whitespace remain unless handled separately.
Counting Occurrences by Comparing Lengths
Subtracting the length after replacement from the original length can count single-character occurrences when both measurements use compatible semantics.
SELECT name,
LENGTH(name) - LENGTH(REPLACE(name, ' ', '')) AS space_count
FROM customers;
The technique becomes more complex for multi-character tokens because the length difference must be divided by the token length. It may also produce misleading results if one function measures bytes and another expression introduces characters with different encoded widths.
Sorting by String Length
LENGTH can be used in ORDER BY to place shorter or longer values first. Add a deterministic secondary sort when several rows share the same measured length.
SELECT employee_id, employee_name
FROM employees
ORDER BY LENGTH(employee_name), employee_name, employee_id;
Reversing the first expression places the longest values first. Sorting a large result by a calculated function can require significant work, so combine it with appropriate filtering and pagination when building user-facing reports.
Distinct Lengths and Length Distribution
DISTINCT can reveal the set of measured lengths present in a column, while GROUP BY shows how many rows occur at each length.
SELECT LENGTH(employee_name) AS name_length,
COUNT(*) AS employee_count
FROM employees
GROUP BY LENGTH(employee_name)
ORDER BY name_length;
This distribution is more informative than a single average. It can expose clusters, unexpected zero-length values, truncation at a fixed boundary, and unusually long outliers.
Minimum, Maximum, and Average Length
Aggregate functions can summarize a text column after LENGTH converts each non-NULL value to a number.
SELECT MIN(LENGTH(description)) AS shortest_description,
MAX(LENGTH(description)) AS longest_description,
AVG(LENGTH(description)) AS average_description_length
FROM products;
These aggregates normally ignore NULL length results. Report the NULL count separately so that a clean-looking average does not conceal many missing descriptions. Consider percentiles or distributions when extreme outliers make an average unrepresentative.
Retrieving the Longest Actual Value
MAX of LENGTH returns a number, not the row containing that value. To retrieve an actual longest name, sort and limit the result using syntax supported by the target DBMS.
SELECT employee_id, employee_name
FROM employees
ORDER BY LENGTH(employee_name) DESC, employee_id
FETCH FIRST 1 ROW ONLY;
PostgreSQL and MySQL commonly use LIMIT, SQL Server supports TOP and OFFSET/FETCH, and modern Oracle supports FETCH FIRST. Decide how ties should be handled; returning one arbitrary row may not satisfy a report that requires every value sharing the maximum length.
Classifying Text with CASE
CASE can translate numeric length ranges into readable categories for profiling and reports.
SELECT employee_name,
CASE
WHEN employee_name IS NULL THEN 'Missing'
WHEN LENGTH(employee_name) <= 5 THEN 'Short'
WHEN LENGTH(employee_name) <= 10 THEN 'Medium'
ELSE 'Long'
END AS name_category
FROM employees;
Testing NULL first avoids treating unknown text as long through the ELSE branch. Category boundaries should be documented because labels such as short and long otherwise have no stable business meaning.
LENGTH Versus COUNT
LENGTH measures one string expression. COUNT aggregates rows or counts non-NULL expression values. They solve different problems even though both return numbers.
-- One measured value per employee
SELECT employee_name, LENGTH(employee_name)
FROM employees;
-- One aggregate row count
SELECT COUNT(employee_name)
FROM employees;
The second query does not count letters. It counts rows where employee_name is not NULL. To calculate the total measured length across rows, use SUM(LENGTH(employee_name)) and define how NULL values should be handled.
Character Length Versus Byte Length
A character is a logical text unit, while a byte is a storage or encoding unit. ASCII characters commonly use one byte in UTF-8, so introductory examples often make both counts look identical. Multilingual characters and emoji can require multiple bytes.
If a username may contain at most 20 characters, use character semantics. If a network field may contain at most 100 encoded bytes, use byte semantics and confirm the encoding. Mixing these requirements can reject valid multilingual names or permit a payload that exceeds a technical limit.
Unicode and User-Perceived Characters
Even a character-counting function may not equal the number of symbols a user perceives. A displayed letter can be represented as one precomposed code point or as a base letter followed by a combining mark. Some emoji are sequences joined into one visible symbol.
Database functions typically count bytes, code units, or code points according to documented semantics, not grapheme clusters as understood by a user interface. Human-facing limits should therefore be tested with realistic multilingual and emoji input in both the application and database.
MySQL LENGTH and CHAR_LENGTH
In MySQL, LENGTH returns the number of bytes in the string. CHAR_LENGTH, also available as CHARACTER_LENGTH, returns the number of characters.
SELECT LENGTH(value) AS byte_length,
CHAR_LENGTH(value) AS character_length
FROM multilingual_text;
For plain ASCII, both results are often equal. For UTF-8 text, byte length can be greater. MySQL applications should use CHAR_LENGTH for character-based validation and LENGTH when encoded byte length is the actual requirement.
PostgreSQL LENGTH and OCTET_LENGTH
PostgreSQL LENGTH on character string input returns the number of characters. OCTET_LENGTH returns bytes, and BIT_LENGTH returns bits.
SELECT LENGTH(value) AS character_length,
OCTET_LENGTH(value) AS byte_length,
BIT_LENGTH(value) AS bit_length
FROM multilingual_text;
The explicit function names make the intended unit visible during review. Keep that precision when translating PostgreSQL SQL to a platform where LENGTH means bytes.
Oracle LENGTH Variants
Oracle LENGTH uses character semantics and provides related variants for other units, including LENGTHB for bytes. Availability and supported data types vary across LENGTHB, LENGTHC, LENGTH2, and LENGTH4.
Oracle also treats an empty character string as NULL in SQL, so LENGTH of an empty string is NULL rather than zero. Migration tests must cover both length-unit semantics and this empty-string behavior.
SQL Server LEN and DATALENGTH
SQL Server commonly uses LEN to count characters and DATALENGTH to return bytes used by an expression. LEN excludes trailing spaces, which is a major difference from many LENGTH examples.
SELECT LEN(value) AS character_length_without_trailing_spaces,
DATALENGTH(value) AS storage_bytes
FROM text_samples;
DATALENGTH depends on the data type and encoding. An nvarchar value typically uses different byte storage from varchar. Neither function should be chosen solely because its name looks familiar from another DBMS.
Cross-Database Comparison
The following table summarizes common functions, but exact behavior should always be checked against the supported database version and column type.
| Database | Function | Typical meaning | Important note |
|---|---|---|---|
| MySQL | LENGTH | Bytes | Use CHAR_LENGTH for characters |
| PostgreSQL | LENGTH | Characters for text | Use OCTET_LENGTH for bytes |
| Oracle | LENGTH | Characters | Empty string is treated as NULL |
| SQL Server | LEN | Characters | Excludes trailing spaces |
| SQL Server | DATALENGTH | Bytes | Depends on the data type |
LENGTH Is Not Automatically Storage Size
A text value containing five characters does not necessarily occupy five bytes on disk. Encoding, fixed-width types, compression, row overhead, large-object storage, indexes, and internal database representation all affect physical storage.
Use a byte-length function for the encoded expression when that is the documented requirement, but do not call it complete table-storage analysis. Capacity planning should use database-specific storage documentation and measured system statistics.
Using LENGTH in Constraints
A CHECK constraint can enforce a permanent length rule close to the data. The exact character-counting function should match the DBMS.
CREATE TABLE app_user (
user_id INTEGER PRIMARY KEY,
username VARCHAR(100) NOT NULL,
CONSTRAINT ck_app_user_username_length
CHECK (CHAR_LENGTH(username) BETWEEN 5 AND 20)
);
Database constraints protect data written by every application, script, and integration. Application validation is still useful for immediate feedback. Both layers should implement the same normalization and character semantics so users do not receive contradictory results.
Performance of Function-Based Filtering
A predicate such as LENGTH(customer_code) = 10 applies a function to the indexed column. A normal index on the original value may not directly support that expression, causing the database to calculate length for many rows.
SELECT customer_id, customer_code
FROM customers
WHERE LENGTH(customer_code) = 10;
For occasional profiling, this may be acceptable. For a frequent, selective production query, investigate an expression index, function-based index, generated column, computed column, or schema constraint supported by the DBMS. Confirm the execution plan with representative data rather than assuming an optimization.
Computed Lengths and Indexing
A stored or virtual generated column can expose a normalized length for constraints and indexes. The syntax and determinism rules differ by database.
-- Conceptual pattern; adapt to the target DBMS
ALTER TABLE products
ADD description_length AS (CHAR_LENGTH(description));
This approach is justified when the measured value supports important repeated workloads. It adds schema complexity and must preserve the exact semantics expected by every query, including Unicode, NULL, and whitespace behavior.
Avoiding Repeated Calculations
Calculating the same expression once in a common table expression can improve readability and reduce the chance that slightly different rules appear in one query. The optimizer decides whether physical evaluation is reused.
WITH measured_products AS (
SELECT product_id,
description,
LENGTH(description) AS description_length
FROM products
)
SELECT product_id, description_length
FROM measured_products
WHERE description_length > 100
ORDER BY description_length DESC;
Use this structure to make the logical rule clear, not as a guaranteed performance optimization. Review the execution plan when efficiency matters.
Data Profiling Workflow
A practical profiling exercise starts by counting total, NULL, and empty values. It then calculates minimum, maximum, average, and grouped distributions, followed by inspection of outliers and whitespace differences.
Profile character and byte lengths separately for multilingual or integration-sensitive columns. Document the selected unit, DBMS behavior, trim policy, normalization policy, and expected limits. This turns an isolated LENGTH query into a repeatable quality check.
Import and Migration Checks
Length comparisons help detect truncation and encoding changes during migrations. Measure values in the source and target using equivalent semantics, then compare counts and outliers rather than assuming that functions with the same name are equivalent.
Test leading and trailing spaces, empty strings, NULL, accented text, non-Latin scripts, emoji, the declared maximum, and values just beyond it. A successful ASCII sample does not demonstrate safe multilingual migration.
API and File Export Limits
External systems may define limits in characters, bytes, or encoded payload size. A database character count does not include JSON escaping, XML markup, CSV quoting, URL encoding, or transport compression.
Use SQL LENGTH functions to inspect source values, but validate the final serialized representation at the integration boundary. This is especially important when a quote, backslash, newline, or Unicode character expands during serialization.
Normalization and Canonically Equivalent Text
Unicode can represent visually equivalent text in different normalized forms. Those forms can have different code point and byte counts even when a user sees the same word.
When stable comparison and length limits matter, establish a normalization policy before storage or validation. Database support varies, so normalization may belong in the application or ingestion pipeline. Measure after the chosen normalization step.
Testing LENGTH Logic
A robust test set includes ordinary ASCII, internal spaces, leading and trailing spaces, empty text, NULL, the minimum and maximum boundaries, text one unit outside each boundary, accented letters, non-Latin scripts, combining marks, and emoji sequences.
Run the tests on every supported DBMS and relevant data type. Verify both returned values and execution plans for important filters. Automated tests should state whether expected results concern characters, bytes, code points, or user-perceived symbols.
Common Mistakes
The most common mistake is assuming that LENGTH always means characters. MySQL immediately disproves that assumption because its LENGTH function returns bytes. Another mistake is ignoring spaces or expecting NULL to produce zero.
Teams also confuse LENGTH with COUNT, rely on length as complete format validation, apply functions to large indexed columns without checking plans, and treat storage bytes as visible characters. These errors disappear when the unit, input state, and business rule are written explicitly.
Best-Practice Checklist
- Define whether the rule measures characters, bytes, storage, or a final payload.
- Use the function documented for that unit in the target DBMS.
- Specify how NULL and empty strings should be classified.
- Decide whether leading, trailing, and internal whitespace is meaningful.
- Normalize or transform text before measuring when the rule applies to transformed output.
- Combine length checks with format and business validation.
- Use database constraints for permanent integrity rules where appropriate.
- Inspect execution plans for frequent function-based filters and sorts.
- Test multilingual text, combining marks, emoji, and boundary values.
- Revalidate semantics during database migrations.
Interview Questions and Answers
What does LENGTH do in SQL? It measures a string according to the target database's documented length semantics.
Does LENGTH count spaces? Spaces can contribute to length, although trailing-space behavior differs by function, DBMS, and data type.
What does LENGTH(NULL) return? It normally returns NULL because the input length is unknown.
What is the difference between LENGTH and COUNT? LENGTH measures a string expression, while COUNT aggregates rows or non-NULL values.
What is the difference between MySQL LENGTH and CHAR_LENGTH? LENGTH returns bytes; CHAR_LENGTH returns characters.
What does PostgreSQL LENGTH return for text? It returns a character count. OCTET_LENGTH returns bytes.
What does SQL Server use? SQL Server commonly uses LEN for characters and DATALENGTH for bytes. LEN excludes trailing spaces.
Can LENGTH be used in WHERE? Yes, but applying a function to an indexed column can affect index use and should be checked with an execution plan.
How do you find the longest value? Use MAX of the appropriate length function for the numeric maximum, or sort by length descending and limit the result to retrieve actual rows.
Is length enough to validate a phone number or email? No. It must be combined with format, allowed-character, and business rules.
Key Takeaway
LENGTH is useful for measuring, validating, profiling, sorting, and cleaning string data, but the function name alone does not define the unit. Reliable SQL first decides whether the requirement concerns characters or bytes and then chooses the correct database-specific function.
RELIABLE STRING MEASUREMENT = DEFINED UNIT
+ KNOWN DBMS SEMANTICS
+ EXPLICIT NULL POLICY
+ DELIBERATE WHITESPACE RULES
+ UNICODE AND BOUNDARY TESTING
+ VERIFIED QUERY PERFORMANCE
For simple ASCII, character and byte counts often look identical. Real systems contain multilingual text, encoded payloads, padded values, missing data, and platform differences. Ask what must actually be measured before writing the query.