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
guests
guest_idusername
1sky_walker
2jo
3
4NULL
5a 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.

expressionresult
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
guests
guest_idusernamepostal_code
1sky_walker02110
2jo941
310001
4NULL75003
5a b1000
Result
guest_idpostal_codecode_len
29413
510004

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)
guests
guest_idusernamepostal_code
1sky_walker02110
2jo941
310001
4NULL75003
5a b1000
Result
code_lenguest_count
31
41
53

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?

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_idusernameLENGTH(username)LENGTH(username) < 3
1sky_walker10false
2jo2true
3''0true
4NULLNULLNULL
5a b3false

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.