SQL CHARACTER_LENGTH

What does SQL CHARACTER_LENGTH do?

Returns the number of characters in a string or text expression.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CHARACTER_LENGTH:

SQL CHARACTER_LENGTH Full Explanation

CHARACTER_LENGTH is a scalar string function defined in the SQL standard that counts the number of characters in a string, not bytes. It works on CHAR, VARCHAR, TEXT and similar data types. The function ignores multibyte‐encoding details, so a 3-byte UTF-8 character counts as 1. On fixed-length CHAR columns, trailing spaces are usually included in the count unless the DBMS automatically pads or trims them. CHARACTER_LENGTH is synonymous with CHAR_LENGTH in many systems, and with LENGTH in some dialects, but the standard form is CHARACTER_LENGTH. The return type is an integer representing character count. The function is deterministic, inexpensive, and can be indexed in predicates.

SQL CHARACTER_LENGTH Syntax

CHARACTER_LENGTH(string_expression);

SQL CHARACTER_LENGTH Parameters

string_expression (string) - Any character or text expression whose length you want to measure

Example Queries Using SQL CHARACTER_LENGTH

-- Basic usage
SELECT CHARACTER_LENGTH('Galaxy') AS len;  -- returns 6

-- Validate that usernames are not longer than 20 chars
SELECT username FROM users
WHERE CHARACTER_LENGTH(username) > 20;

-- Compare byte length vs character length (PostgreSQL example)
SELECT OCTET_LENGTH('€')  AS bytes,
       CHARACTER_LENGTH('€') AS chars;

Expected Output Using SQL CHARACTER_LENGTH

The queries return an integer value for each input row
In the first example, the output is a single column named len with value 6
In the second example, rows where username exceeds 20 characters are returned
In the byte vs character example, bytes = 3 and chars = 1

Use Cases with SQL CHARACTER_LENGTH

Enforce maximum field lengths in data validation
Detect unusually long text that may indicate data quality issues
Count characters for UI display truncation
Compare storage size (bytes) versus visible length (characters)
Build analytics on average text length per record

Common Mistakes with SQL CHARACTER_LENGTH

Confusing CHARACTER_LENGTH with OCTET_LENGTH and assuming byte count equals character count
Using LENGTH in dialects where it measures bytes, not characters
Forgetting that fixed-length CHAR columns may include padded spaces
Assuming NULL input returns 0 – it actually returns NULL
Expecting the function to trim whitespace automatically

First Introduced In

SQL-92

Frequently Asked Questions

What is the difference between CHARACTER_LENGTH and LENGTH?

In many databases they are synonyms, but some dialects use LENGTH to count bytes. CHARACTER_LENGTH is standard and always counts characters.

Does CHARACTER_LENGTH work with UTF-8 and other multibyte encodings?

Yes. It returns the number of characters, not bytes, so multibyte characters count as 1 each.

How does the function treat NULL input?

If the input expression is NULL, the result is NULL.

How can I exclude trailing spaces in a CHAR column?

Use TRIM(column) inside CHARACTER_LENGTH, e.g., CHARACTER_LENGTH(TRIM(my_char_col)).