CHAR_LENGTH

Return the number of characters in the input string.

Category: stringReturns: INTEGERDialect: Standard

Syntax

CHAR_LENGTH(str)

Description

## Overview Returns the number of characters in the input string. CHAR_LENGTH is the SQL-standard spelling and is functionally equivalent to LENGTH and CHARACTER_LENGTH for string inputs. Multi-byte UTF-8 characters count as a single character. Use CHAR_LENGTH when you want to make the intent explicit (a character count rather than a byte count) or when porting code from dialects that reserve LENGTH for byte counting. ## Behavior - Returns NULL when the input is NULL. - Returns 0 for an empty string. - Each Unicode code point counts as one character. Multi-byte UTF-8 sequences are not double-counted. - Combining sequences (base character plus combining marks) count as the number of code points, not grapheme clusters. - Trailing whitespace is counted; it is not trimmed implicitly. - Never returns a negative value. - Identical in behaviour to LENGTH and CHARACTER_LENGTH when applied to character strings. ## Compatibility - Conforms to the SQL standard CHAR_LENGTH definition. - CHARACTER_LENGTH is the full-length SQL-standard alias and is also supported.

Parameters

Name	Type	Description
`str`		Specifies the input string whose character count is returned. Accepts any VARCHAR or CHAR value.

Examples

-- ASCII string
SELECT CHAR_LENGTH('hello');  -- 5

-- Empty string
SELECT CHAR_LENGTH('');  -- 0

-- Multi-byte characters count as one
SELECT CHAR_LENGTH('élève');  -- 5

-- NULL propagates
SELECT CHAR_LENGTH(CAST(NULL AS VARCHAR));  -- NULL

-- Enforce a maximum code length on insert
SELECT order_id, product_code
FROM retail.sales.orders
WHERE CHAR_LENGTH(product_code) > 12;

-- Distribution of description length buckets
SELECT CASE WHEN CHAR_LENGTH(description) < 50 THEN 'short'
            WHEN CHAR_LENGTH(description) < 200 THEN 'medium'
            ELSE 'long' END AS bucket,
       COUNT(*)
FROM retail.products.catalog
GROUP BY 1;

Pitfalls

CHAR_LENGTH counts Unicode code points, not grapheme clusters. A base letter plus a combining accent is 2 characters even though it renders as one.
CHAR_LENGTH differs from OCTET_LENGTH for any string that contains non-ASCII characters. Do not use CHAR_LENGTH for storage-size estimates.
Trailing spaces in a CHAR(n) value are counted. If you want the 'logical' length of a padded CHAR, apply RTRIM first.
CHAR_LENGTH on a column sometimes blocks index usage in predicates. Prefer a persisted or generated length column if you filter on this frequently.