LENGTH()
Concepts covered: String Length
LENGTH counts the characters in a string
LENGTH(text) returns an INTEGER: the number of characters the string holds. Every character counts, including spaces and punctuation, so LENGTH('a b') is 3. LENGTH('') is 0, because the empty string exists and holds no characters. LENGTH(NULL) is NULL, because there is no string to measure. That split carries the empty string versus NULL distinction into measurement: an empty username has a size, a missing one has no size at all. The guests table has one of each.
SELECT
guest_id,
username,
LENGTH(username) AS name_len
FROM guests| guest_id | username |
|---|---|
| 1 | sky_walker |
| 2 | jo |
| 3 | |
| 4 | NULL |
| 5 | a b |
Guest 3 typed nothing and gets 0. Nobody ever filled the field for guest 4, so that row gets NULL. Guest 5 typed a space between 2 letters and gets 3, because the space is a character. The reference table below adds a few values that come up constantly in real data.
| expression | result |
|---|---|
| LENGTH('sky_walker') | 10 |
| LENGTH('jo') | 2 |
| LENGTH('') | 0 |
| LENGTH(NULL) | NULL |
| LENGTH('a b') | 3 |
| LENGTH(' hi ') | 6 |
| LENGTH('café') | 4 |
Characters, not bytes
LENGTH counts characters as a reader sees them. An accented letter or a character from another writing system counts as 1: LENGTH('café') is 4 and LENGTH('東京') is 2, even though those strings take more bytes of storage than 4 or 2 plain letters would. If a report says a title has 20 characters, a person counting on screen gets 20 as well, which is the behavior you want when checking a field against a form's character limit.
LENGTH in WHERE finds malformed values
Because LENGTH returns a number, every comparison operator from the filtering lesson works on it: =, !=, <, >, IN. The signup form accepts only 5-digit postal codes, so LENGTH(postal_code) != 5 flags every row the form should have rejected. The guests table has 2 such rows, and the query shows the measured length next to each one so you can see by how much it misses.
SELECT
guest_id,
postal_code,
LENGTH(postal_code) AS code_len
FROM guests
WHERE LENGTH(postal_code) != 5| guest_id | username | postal_code |
|---|---|---|
| 1 | sky_walker | 02110 |
| 2 | jo | 941 |
| 3 | 10001 | |
| 4 | NULL | 75003 |
| 5 | a b | 1000 |
| guest_id | postal_code | code_len |
|---|---|---|
| 2 | 941 | 3 |
| 5 | 1000 | 4 |
Guest 2's code is missing 2 digits and guest 5's is missing 1. A filter like this is a data quality check you can run every morning: a result of 0 rows means the signup form is doing its job, and any row that appears names the exact record to fix.
LENGTH with GROUP BY and aggregates
GROUP BY LENGTH(postal_code) puts every row into a bucket by how long its code is, and COUNT(*) sizes each bucket. The same expression appears in the SELECT list and in GROUP BY, as any grouping expression must. The result tells you the shape of the column: in a clean table every row lands in the 5 bucket, and a wide spread of lengths means the form accepts anything.
SELECT
LENGTH(postal_code) AS code_len,
COUNT(*) AS guest_count
FROM guests
GROUP BY LENGTH(postal_code)| guest_id | username | postal_code |
|---|---|---|
| 1 | sky_walker | 02110 |
| 2 | jo | 941 |
| 3 | 10001 | |
| 4 | NULL | 75003 |
| 5 | a b | 1000 |
| code_len | guest_count |
|---|---|
| 3 | 1 |
| 4 | 1 |
| 5 | 3 |
The 3 result rows can arrive in any order. A query with GROUP BY makes no promise about the sequence of its groups.
LENGTH also goes inside the aggregates you already have. AVG(LENGTH(title)) gives the typical size of a listing title, MAX(LENGTH(title)) finds the longest one, and MIN(LENGTH(title)) catches a title someone saved as a single letter. Each of these treats the length as any other number, so a NULL title drops out of the average the same way a NULL price would.
What does LENGTH(' hi ') return, where the string has 2 spaces before hi and 2 spaces after it?
- 6, because every space is a character and each one counts
- 2, because LENGTH counts only letters and digits in the string
- 4, because leading spaces are dropped but trailing ones are kept
- NULL, because padding spaces make the value malformed
Filtering for values that are too short
LENGTH(username) < 3 keeps every username with fewer than 3 characters. Walk it through the guests table and NULL decides one of the outcomes. Guest 2's 'jo' measures 2 and passes. Guest 3's empty string measures 0 and passes too, so an empty value counts as too short. Guest 4's username is NULL, its length is NULL, and NULL < 3 is unknown, so WHERE drops the row. Guest 5's 'a b' measures 3, and 3 < 3 is false. A check for short values misses missing ones, and those need their own IS NULL check.
| guest_id | username | LENGTH(username) | LENGTH(username) < 3 |
|---|---|---|---|
| 1 | sky_walker | 10 | false |
| 2 | jo | 2 | true |
| 3 | '' | 0 | true |
| 4 | NULL | NULL | NULL |
| 5 | a b | 3 | false |
About this lesson
This section is part of the String Functions: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.