Oracle’s LENGTH function returns the length of a character value in characters: for example, LENGTH('Oracle') returns 6. To count bytes instead, use LENGTHB; Oracle also provides LENGTHC, LENGTH2, and LENGTH4 for particular Unicode counting rules.
What LENGTH returns
The basic syntax is LENGTH(char). The argument can be a character expression, such as a quoted string, a table column, or the result of another expression. Oracle returns a number representing the value’s length, measured in characters as defined by the input character set.
For a straightforward example, this query returns 6:
SELECT LENGTH('Oracle') AS string_length
FROM dual;
DUAL is Oracle’s one-row table, often used to evaluate an expression without reading a business table. In application queries, the argument is more often a column, such as LENGTH(customer_name). The result can be selected, compared in a condition, or used in another expression.
One detail matters immediately: LENGTH includes trailing spaces that are part of the value. A string containing the four characters A, B, C, and a final space has length 4. It does not trim the value before counting. This can surprise someone who sees a name displayed without an obvious space at the end.
Characters are not always bytes
A character may occupy more than one byte in the database character set. The distinction is especially important in multibyte character sets, including Oracle’s commonly used AL32UTF8 character set. In that setting, a character such as é occupies two bytes in UTF-8, although it is one character for the ordinary LENGTH count.
For example, in a database using AL32UTF8, the following expression produces a character count of 1 and a byte count of 2:
SELECT LENGTH('é') AS character_count,
LENGTHB('é') AS byte_count
FROM dual;
The byte result depends on the character encoding. It is not safe to assume that every non-ASCII character occupies two bytes, or that every string has the same number of bytes as characters. A value containing different scripts or symbols can have different byte sizes for different characters.
Also, what looks like one symbol on screen is not always stored as one character. An accented letter can be represented as a single precomposed character or as a base letter followed by a combining mark. Similarly, some emoji displayed as one pictograph consist of multiple Unicode characters joined together. The database function counts according to its defined character rules; it does not measure visual width or decide how many symbols a person perceives.
Oracle’s related length functions
Choose the length function that matches the question you are trying to answer. Oracle’s alternatives are not interchangeable in every situation:
LENGTHcounts characters as defined by the input character set. This is the usual choice when asking how many characters are in a value.LENGTHBcounts bytes. It is useful when a requirement concerns encoded storage size or a byte-based limit, but the answer depends on the character set.LENGTHCcounts Unicode complete characters. It can differ from other counts for Unicode text involving supplementary characters.LENGTH2counts UCS-2 code points.LENGTH4counts UCS-4 code points.
For ordinary English text made up of basic Latin characters, these functions often return the same number. That coincidence is not a reason to substitute one for another. If the database later receives multilingual content, the distinction can become significant. In particular, a byte limit must be checked with a byte count, not inferred from LENGTH.
These functions also do not count the width of text on a screen. A narrow letter and a wide letter each contribute to a character count, even though they may occupy different display widths in a font. Nor should LENGTHC be treated as a general-purpose count of user-perceived grapheme clusters: a displayed emoji or accented sequence can still comprise multiple complete characters.
Empty strings, NULL, and trailing spaces
Oracle treats a zero-length character string as NULL in SQL. As a result, LENGTH('') returns NULL, not zero. The same applies when the argument itself is null: LENGTH(NULL) yields null. This differs from systems where an empty string is a distinct value whose length is 0.
That difference affects conditions and calculations. A comparison such as LENGTH(column) = 0 does not identify Oracle empty strings; they are null, and ordinary comparisons with null do not evaluate to true. To find rows with no value, use a null test such as column IS NULL. If an expression should produce zero for a null length, use NVL(LENGTH(column), 0) deliberately, understanding that it maps null to zero for that expression.
Trailing spaces are a separate issue. A VARCHAR2 value can contain spaces at its end, and LENGTH counts spaces that are present. A fixed-width CHAR column is padded with spaces to its declared width, so its length can reflect that padding. For instance, a short word stored in a CHAR(10) value can have a length of 10 because the remaining positions are blank-padded. Display tools may make those spaces hard to notice.
If the business rule is to count after trimming ordinary leading and trailing spaces, express that rule rather than assuming LENGTH trims automatically. For a value in column input_text, LENGTH(TRIM(input_text)) counts the length after Oracle’s trim operation. Trimming changes the value being measured, so it is not suitable when trailing spaces are meaningful data.
Using LENGTH in queries
A common use is to find values that do not meet a stated character length. Suppose a table named members has an identifier column called member_code. This condition selects non-null codes whose character length is exactly eight:
SELECT member_code
FROM members
WHERE LENGTH(member_code) = 8;
To find codes shorter than eight characters, use LENGTH(member_code) < 8; to check a range, use a condition such as LENGTH(member_code) BETWEEN 6 AND 8. Remember that a null column value does not satisfy these numeric comparisons. If nulls need separate handling, add an explicit null condition.
You can also return the count beside the original value:
SELECT member_code,
LENGTH(member_code) AS character_count
FROM members;
This is useful when investigating imported data, unexpected spaces, or inconsistent identifiers. To inspect potential trailing spaces, compare the original count with a trimmed count. A difference indicates that trimming removed characters at the ends, although it does not by itself establish whether those characters are accidental or meaningful.
Limits, data types, and practical cautions
Oracle documents LENGTH for character data types including CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, and NCLOB. The argument’s type and character set matter when interpreting the result. In particular, the byte-counting and Unicode-specific variants have restrictions for large objects: LENGTHB is not supported for CLOB or NCLOB values in a multibyte character set, and LENGTHC, LENGTH2, and LENGTH4 do not accept those LOB types.
Do not confuse the length of a string with the size of a stored column or a complete database row. LENGTH answers a character-count question; LENGTHB answers a byte-count question for supported inputs. Neither is a general measurement of all storage overhead associated with a database value, row, or index.
Another practical consideration is query performance. Applying a function to a column in a condition, as in WHERE LENGTH(member_code) = 8, may prevent Oracle from using an ordinary index on that column as a direct lookup for the condition. If this check is frequent on a large table, examine the execution plan and database indexing options with the workload in mind. Oracle supports function-based indexes, but adding one is a schema and maintenance decision, not an automatic requirement for every length check.
How to choose the right function
Start with the requirement, not with a guess about what the word length means. If a form asks for a maximum number of characters, LENGTH is generally the relevant starting point, but verify the application’s exact Unicode and validation rules. If a file format, protocol, or column limit is expressed in bytes, a character count is insufficient; use an appropriate byte count and check the relevant character set and data type.
Use this quick guide:
- Counting a typical SQL string for display or a character-based rule: use
LENGTH. - Checking a byte-based requirement: use
LENGTHBwhere supported, and test with the database character set in use. - Working with a requirement explicitly based on Unicode complete characters, UCS-2 code points, or UCS-4 code points: choose
LENGTHC,LENGTH2, orLENGTH4as specified. - Ignoring surrounding spaces on purpose: apply
TRIMbefore measuring, rather than expecting the length function to remove them. - Distinguishing absent data from a genuinely measured zero: handle
NULLexplicitly, since Oracle treats an empty string as null.
For validation shared between Oracle and another programming language, test representative inputs in both places. Include basic Latin text, accented text, supplementary Unicode characters, combining marks, and trailing spaces if those can occur. Different systems may define “character” differently, so a matching function name does not guarantee matching results.
Frequently Asked Questions
What is the Oracle function to find the length of a string?
Use LENGTH(string). For example, LENGTH('Oracle') returns 6.
Does Oracle LENGTH count spaces?
Yes. Spaces that are part of the value, including trailing spaces, are counted. A fixed-width CHAR value may also include padding spaces.
Why does LENGTH(”) return NULL instead of 0?
Oracle SQL treats a zero-length character string as NULL. Since the length of a null argument is null, the expression does not return zero.
What is the difference between LENGTH and LENGTHB?LENGTH counts characters according to the input character set; LENGTHB counts bytes. In a multibyte character set, one character can occupy multiple bytes.
How do I count a string after removing spaces at the ends?
Use LENGTH(TRIM(value)) when the intended rule is to remove surrounding spaces before counting. Do not do this if those spaces are meaningful data.
Does LENGTH count an emoji as one character?
Not necessarily as a person sees it on screen. Some displayed emoji are sequences of Unicode characters, and Oracle’s length functions use defined encoding and Unicode counting rules rather than visual-symbol counting.
Related
- JavaScript String Split: Syntax, Examples, and Tips
- JavaScript split(): How to Use It and Avoid Pitfalls
- Python Logging F-Strings: When to Use Them and Why
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sales Comparison Formula in Excel: Examples & TipsFor a sales comparison in Excel, calculate the change as new sales minus old sales, then divide that…
- How to Filter by Number in Excel: A Step-by-Step GuideTo filter by number in Excel, select a cell in your data, choose Data > Filter, open the…
- Unique List From Excel Column: Formulas and OptionsTo create a unique list from an Excel column in Microsoft 365, enter =UNIQUE(A2:A100) in an empty cell,…
- Excel Formula for Creating a CalendarThe simplest Excel formula for creating a calendar is =SEQUENCE(6,7) combined with date arithmetic, but the most practical…
- COUNTIF Examples: Formulas for Common Spreadsheet TasksUse Excel’s COUNTIF function to count cells in one range that meet one condition: =COUNTIF(range,criteria). For example, it…
- Excel COUNTIF Within a Year: Date Range FormulaTo count cells with dates within a specific year in Excel, use COUNTIFS with two date criteria: =COUNTIFS(A2:A100,">="&DATE(2024,1,1),A2:A100,"=1/1/2024").…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.