# SQL LENGTH Function: Syntax and Examples for String Length

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:

```sql
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.

```sql
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:

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

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

```sql
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](/blog/data-analysis/sql-in-operator-syntax-examples) handles value lists, and you can stack a length check on top of it:

```sql
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](/blog/data-analysis/sql-count-function) works on the filtered set, and the [SQL MAX function](/blog/data-analysis/sql-max-function-syntax-examples) finds the longest value in a column:

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

**Build a length label.** The [SQL CONCAT function](/blog/data-analysis/sql-concat-function) lets you attach the count to the value for reporting:

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

**SQL Server equivalent.** The same filtering logic uses `LEN`:

```sql
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.

## References

1. [LEN (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql?view=sql-server-ver17)
2. [length function - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/functions/length)
3. [DATALENGTH (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/datalength-transact-sql?view=sql-server-ver17)
4. [PostgreSQL: Documentation: 18: 9.4. String Functions and Operators](https://www.postgresql.org/docs/current/functions-string.html)

## Further Reading

- [Codd EF (1970). A relational model of data for large shared data banks. Communications of the ACM](https://doi.org/10.1145/362384.362685)
- [PostgreSQL Documentation: SELECT](https://www.postgresql.org/docs/current/sql-select.html)
- [SQLite: SQL As Understood By SQLite](https://www.sqlite.org/lang.html)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)

## Related Articles

- [SQL DECODE Function: Syntax and Examples](/blog/data-analysis/sql-decode-function-syntax-examples)
- [SQL MAX Function: Syntax and Examples](/blog/data-analysis/sql-max-function-syntax-examples)
- [SQL RIGHT Function: Syntax, Examples and Use Cases](/blog/data-analysis/sql-right-function-syntax-examples)
- [SQL ROW_NUMBER Function: Syntax and Examples](/blog/data-analysis/sql-row-number-function)
- [SQL MOD Function: Syntax, Examples and Use Cases](/blog/data-analysis/sql-mod-function)
- [Read Length in DNA Sequencing: Impact, Trade-offs, and Best Practices](/knowledge/molecular-biology/read-length)