SQL CHAR_LENGTH

What is SQL CHAR_LENGTH and how does it work?

Returns the number of characters in a string, not the number of bytes.

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 CHAR_LENGTH:

SQL CHAR_LENGTH Full Explanation

CHAR_LENGTH (also written as CHARACTER_LENGTH) is a standard SQL scalar function that counts the number of characters in its input string and returns that count as an integer. It measures logical characters, so multi-byte UTF-8 or UTF-16 code points are counted as one character each, making it different from OCTET_LENGTH or DATALENGTH functions that measure bytes. CHAR_LENGTH ignores trailing spaces in some systems (for example, MySQL with PAD SPACE mode off) but counts them in others (PostgreSQL). The function works on CHAR, VARCHAR, TEXT, and similar character data types. If the argument is NULL the function returns NULL. When a binary string is supplied, most engines raise a type error.

SQL CHAR_LENGTH Syntax

CHAR_LENGTH(string_expression);
-- or
CHARACTER_LENGTH(string_expression);

SQL CHAR_LENGTH Parameters

• string_expression (STRING) - Any character expression such as a literal, column, or expression; can be CHAR, VARCHAR, TEXT, NCHAR, NVARCHAR, etc.

Example Queries Using SQL CHAR_LENGTH

-- Basic usage
SELECT CHAR_LENGTH('Galaxy');
-- Multibyte characters (emoji counts as one)
SELECT CHAR_LENGTH('🚀Galaxy');
-- Column usage
SELECT username, CHAR_LENGTH(username) AS name_len
FROM users
WHERE CHAR_LENGTH(username) > 15;

Expected Output Using SQL CHAR_LENGTH

First query returns 6
Second query returns 7 because the rocket emoji is a single character
Third query lists each username with its length and filters to those longer than 15 characters

Use Cases with SQL CHAR_LENGTH

Validate maximum allowed lengths before inserting or updating data.
Calculate display widths for UI rendering.
Enforce password or username length rules in CHECK constraints.
Trim or split strings based on character count in ETL pipelines.
Compare character vs byte sizes when storing UTF-8 data.

Common Mistakes with SQL CHAR_LENGTH

Assuming CHAR_LENGTH counts bytes – use OCTET_LENGTH or DATALENGTH for that.
Forgetting that NULL input returns NULL, not 0.
Expecting SQL Server or Oracle to recognize CHAR_LENGTH without enabling ANSI standard compatibility (they use LEN and LENGTH respectively).
Misinterpreting results when trailing spaces are trimmed by the database's PAD SPACE setting.

First Introduced In

SQL:1999 standard

Frequently Asked Questions

What is the difference between CHAR_LENGTH and OCTET_LENGTH?

CHAR_LENGTH counts characters, while OCTET_LENGTH counts bytes, so UTF-8 characters occupying multiple bytes still count as one in CHAR_LENGTH.

Why does CHAR_LENGTH return NULL instead of 0 for empty input?

It only returns NULL when the input itself is NULL. For an empty string ('') it correctly returns 0.

Does CHAR_LENGTH trim trailing spaces?

Behavior depends on the database. PostgreSQL counts them, while MySQL may ignore them unless SQL mode PAD SPACE is enabled.

How can I get character length in SQL Server or Oracle?

Use LEN(string) in SQL Server and LENGTH(string) in Oracle, as CHAR_LENGTH is not implemented natively in those systems.