SQL LENGTH() Function: Syntax, Usage, and Examples

The SQL LENGTH function returns the number of characters in a string, including spaces and special symbols. It’s a handy tool when you need to validate data, check formatting, or process text-based fields in queries.

How to Use the LENGTH() Function in SQL

The LENGTH() function works with string expressions and returns an integer representing the number of characters in that string.

Basic Syntax

LENGTH(string)

• string: The text or column you want to measure.

Example

SELECT LENGTH('hello world') AS string_length;

This returns 11, counting each letter and space.

Note: In some databases like Oracle, the function is LENGTH(), while in others like SQL Server it’s LEN().

When to Use SQL LENGTH

You’ll often use SQL LENGTH when working with text validation, string truncation, or filtering rows based on text size.

Validate Field Lengths

Use it to check whether a field meets minimum or maximum character requirements.

SELECT name
FROM users
WHERE LENGTH(name) > 20;

This finds users with long names.

Filter Rows with Short Text

Sometimes, you only want rows where a column has meaningful text.

SELECT message
FROM feedback
WHERE LENGTH(message) > 0;

This excludes empty or blank comments.

Control Output Format

When displaying or exporting data, you might want to pad, trim, or truncate based on the string’s length.

SELECT
  name,
  LENGTH(name) AS name_length
FROM employees;

Now you can see if formatting adjustments are needed.

Examples of SQL LENGTH in Practice

Example 1: Finding Short Product Names

SELECT product_name
FROM products
WHERE LENGTH(product_name) < 10;

Returns product names shorter than 10 characters.

Example 2: Check Length Before Update

UPDATE users
SET username = 'guest'
WHERE LENGTH(username) = 0;

This sets blank usernames to 'guest'.

Example 3: Use in Conditional Logic

SELECT
  email,
  CASE
    WHEN LENGTH(email) < 5 THEN 'Too short'
    WHEN LENGTH(email) > 50 THEN 'Too long'
    ELSE 'OK'
  END AS status
FROM contacts;

This flags emails that don’t fall within an acceptable range.

Example 4: Sort by Length

SELECT title
FROM blog_posts
ORDER BY LENGTH(title) DESC;

Sorts blog titles from longest to shortest.

Example 5: Calculate Total Length of Two Fields

SELECT
  first_name,
  last_name,
  LENGTH(first_name) + LENGTH(last_name) AS full_length
FROM people;

Helps assess combined name lengths, useful for formatting name badges or reports.

Learn More About SQL LENGTH

SQL LENGTH vs CHAR_LENGTH

In MySQL, you might see both LENGTH() and CHAR_LENGTH():

• LENGTH() returns the number of bytes.
• CHAR_LENGTH() returns the number of characters.

This difference matters with multibyte character sets like UTF-8.

SELECT LENGTH('ñ'), CHAR_LENGTH('ñ');

The first might return 2, the second 1.

Using LENGTH with TRIM

Sometimes whitespace inflates the string length. Combine LENGTH with TRIM to get accurate measurements.

SELECT LENGTH(TRIM(name)) AS trimmed_length
FROM users;

This removes extra spaces before measuring.

SQL LENGTH with WHERE Clauses

You can use the LENGTH function in WHERE clauses to filter by the length of a string.

SELECT comment
FROM reviews
WHERE LENGTH(comment) BETWEEN 50 AND 100;

Returns moderately sized reviews, good for reports.

Apply SQL LENGTH to Table Columns

SELECT
  article_title,
  LENGTH(article_title) AS title_length
FROM articles;

This quickly helps spot outliers—either titles that are too short or ones that overflow UI designs.

SQL LENGTH and Data Cleaning

When preparing data, LENGTH can help you clean up inconsistencies.

DELETE FROM users
WHERE LENGTH(username) < 3;

You’re removing junk values or placeholders.

Combine LENGTH with LEFT or RIGHT

LENGTH plays well with string-slicing functions. For instance:

SELECT
  LEFT(description, LENGTH(description) - 10) AS truncated_description
FROM news;

This removes the last 10 characters from each row.

SQL LENGTH in Subqueries

You can use LENGTH in a subquery for dynamic filtering.

SELECT *
FROM files
WHERE LENGTH(filename) = (
  SELECT MAX(LENGTH(filename))
  FROM files
);

This gives you the file with the longest name.

SQL LENGTH vs DATALENGTH in SQL Server

SQL Server uses LEN() and DATALENGTH():

• LEN() excludes trailing spaces.
• DATALENGTH() includes everything (and returns bytes).

SELECT LEN('abc   '), DATALENGTH('abc   ');

This shows how padding can affect the result.