SQL LENGTH Function: Syntax and Examples for String Length

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

LENGTH in SQL returns the number of characters in a string, so you can measure text values and filter rows by how long they are. The function goes by different names depending on the database: LENGTH in PostgreSQL, SQLite and Databricks SQL, CHAR_LENGTH in MySQL (where LENGTH counts bytes), and LEN in SQL Server. This article covers the syntax, a worked filtering example, and the traps that trip people up when they check the length of a string in SQL.

Quick Answer

LENGTH(string) returns the character count of a string. SQL Server calls the same idea LEN(string) [1].
Trailing spaces behave differently by engine. SQL Server's LEN excludes trailing spaces, while Databricks length includes them [1][2].
To count bytes instead of characters, use DATALENGTH in SQL Server [3].
You can use the result in WHERE, ORDER BY, or SELECT to filter and sort by string size.
NULL input returns NULL, so rows with missing values drop out of comparisons.

Syntax

The general form is simple:

LENGTH(string_expression)

Argument	Required?	Meaning
`string_expression`	Yes	The value to measure. It can be a constant, a variable, or a column of character or binary data [1].

The return type is an integer. In SQL Server, LEN returns bigint when the expression is varchar(max), nvarchar(max), or varbinary(max), and int otherwise [1]. DATALENGTH follows the same rule [3].

Dialect names for the same operation:

Database	Function name
PostgreSQL	`length` [4]
SQLite	`length`
MySQL	`CHAR_LENGTH` (`LENGTH` returns bytes)
SQL Server	`LEN` [1]
Databricks SQL	`length` [2]

Databricks also accepts character_length and char_length as synonyms for length [2].

How It Works

The function walks the string and counts characters. For text data, that count is the number of characters, not the number of bytes. The two differ whenever a character needs more than one byte, which happens with Unicode data. Microsoft's documentation makes the split explicit: use LEN for the number of characters and DATALENGTH for the size in bytes, and the outputs can differ depending on the data type and encoding [1][3].

Trailing spaces are the second thing to understand. SQL Server's LEN excludes trailing spaces, so LEN('abc ') counts 3, not 4. Microsoft notes that if trailing spaces matter, DATALENGTH is the alternative because it does not trim the string [1]. Databricks takes the opposite approach: the length of string data includes trailing spaces, and the length of binary data includes trailing binary zeros [2].

For Unicode strings under supplementary character (SC) collations, SQL Server counts UTF-16 surrogate pairs as a single character [1]. That matters for emoji and some scripts, where a single visible character can occupy two code units.

The result is a plain integer, so you can compare it, sort by it, or feed it into other expressions. That is what makes it useful for data quality checks, such as finding rows where a code column is not the expected width.

Worked Example

The dataset is a small customers table with one row per customer and a name column. Here is the input:

customer_id	name
1	Al
2	Grace
3	Jonathan
4	Priyanka
5	Bo
6	Christopher
7	Mei-Ling
8	Sam

The goal is to list customers whose names are longer than 8 characters, longest first.

SELECT name, LENGTH(name) AS name_length
FROM customers
WHERE LENGTH(name) > 8
ORDER BY name_length DESC;

The query works in four steps:

SELECT name, LENGTH(name) AS name_length returns each customer's name alongside the number of characters in it. LENGTH counts characters, not bytes, in SQLite text values.
FROM customers reads rows from the customers table, which holds one row per customer.
WHERE LENGTH(name) > 8 keeps only rows whose name is longer than 8 characters, filtering by character count instead of by pattern.
ORDER BY name_length DESC sorts the surviving rows from longest name to shortest so the result is easy to scan.

Result:

name	name_length
Christopher	11

Only one name survives the filter. "Christopher" has 11 characters, while "Jonathan" and "Priyanka" have 8 each and are excluded by the strict greater-than comparison. The result was checked with an equivalent SQLite query.

More Examples

Filter by an exact length. To find fixed-width codes that are the wrong size:

SELECT order_id, status_code
FROM orders
WHERE LENGTH(status_code) <> 3;

Sort by string size. To see the longest values first:

SELECT product_name, LENGTH(product_name) AS name_length
FROM products
ORDER BY name_length DESC;

Combine with other conditions. Length filters pair well with pattern filters. The SQL IN operator handles value lists, and you can stack a length check on top of it:

SELECT email
FROM users
WHERE LENGTH(email) > 20
  AND domain IN ('example.com', 'example.org');

Aggregate on length. Once you have a length value, you can summarize it. The SQL COUNT function works on the filtered set, and the SQL MAX function finds the longest value in a column:

SELECT MAX(LENGTH(comment_text)) AS longest_comment
FROM comments;

Build a length label. The SQL CONCAT function lets you attach the count to the value for reporting:

SELECT CONCAT(name, ' (', LENGTH(name), ' chars)') AS label
FROM customers;

SQL Server equivalent. The same filtering logic uses LEN:

SELECT name, LEN(name) AS name_length
FROM customers
WHERE LEN(name) > 8
ORDER BY name_length DESC;

Errors and How to Fix Them

Function does not exist. Calling LENGTH in SQL Server raises an error because the function is named LEN there [1]. Switch the name, or use DATALENGTH if you need bytes [3].

Unexpected count with trailing spaces. If a value looks longer than the number returned, trailing spaces are the likely cause. SQL Server's LEN excludes them [1]. Use DATALENGTH when the stored size matters [3].

Byte count mistaken for character count. On Unicode columns, DATALENGTH returns a number that may not equal the number of characters [1]. Pick the function that matches the question you are asking.

NULL rows disappear. DATALENGTH returns NULL for a NULL value [3], and LENGTH behaves the same way. A WHERE LENGTH(col) > 5 filter silently drops those rows. Add OR col IS NULL if you need to keep them.

Comparing against the wrong type. Passing a number to LENGTH usually triggers an implicit conversion, and the result may not be what you expect. Convert explicitly when the input is not already text.

Common Mistakes

Assuming every database uses LENGTH. SQL Server uses LEN [1]. Check the dialect before copying a query between systems.
Using LENGTH to measure storage size. LENGTH counts characters. DATALENGTH returns bytes, and the two differ for Unicode data [1][3].
Forgetting that trailing spaces are trimmed in SQL Server. LEN('abc ') returns 3 there [1]. If padding is meaningful, use DATALENGTH [3].
Expecting trailing spaces to be trimmed everywhere. Databricks includes trailing spaces in the length of string data [2], so the same value can report different counts across engines.
Filtering on length without handling NULL. Rows with NULL values fail any comparison and vanish from the result. Decide whether that is what you want.
Using LENGTH when you meant a pattern match. Length tells you how many characters there are, not which ones. Use LIKE or a substring function when the content matters.

Limitations

LENGTH answers one narrow question: how many characters are in this value. It cannot tell you whether the characters are the ones you wanted, whether the value is valid, or whether it is unique. A column of ten-character strings can still be full of garbage. Pair length checks with pattern checks when you are validating data.

Cross-engine differences are the other limit. The same string can report different counts in SQL Server and Databricks because of trailing space handling [1][2], and byte counts diverge from character counts on Unicode data [1][3]. If you move a query between platforms, verify the length results on a sample before trusting them in production. For binary data, the function returns a byte count, which is a different measurement entirely [2].

Frequently Asked Questions

What is the difference between LENGTH and LEN in SQL?

They do the same job under different names. LEN is the SQL Server name, and it returns the number of characters in a string expression, excluding trailing spaces [1]. LENGTH is the character-count name used in PostgreSQL, SQLite and Databricks SQL [2][4], while MySQL's LENGTH counts bytes and its character count is CHAR_LENGTH. The behavior around trailing spaces is what actually differs between engines, not the name.

Does LENGTH count spaces?

It depends on the engine and the position of the space. SQL Server's LEN excludes trailing spaces but counts spaces inside the string [1]. Databricks includes trailing spaces in the length of string data [2]. Leading spaces are counted in both cases.

How do I get the length of a string in SQL Server?

Use LEN, as in SELECT LEN(name) FROM customers [1]. If you need the number of bytes instead of characters, use DATALENGTH, which does not trim the string [1][3]. The two return different values for Unicode columns.

Can I use LENGTH in a WHERE clause?

Yes. WHERE LENGTH(name) > 8 keeps only rows whose name is longer than 8 characters, which is a common way to find values outside an expected width. The same expression works in ORDER BY and in SELECT lists. Remember that NULL values fail the comparison and are excluded.

Why does LENGTH return a different number than I expected?

Three common causes. Trailing spaces may be trimmed by the engine [1]. The column may be Unicode, so byte counts and character counts diverge [1][3]. Or the value may contain surrogate pairs that are counted as one character under SC collations [1]. Check the data type and the engine's rules before assuming the function is wrong.