The Oracle SQL function for string length is LENGTH, and you use it as LENGTH(string) to return the number of characters in a string. For example, SELECT LENGTH('Hello') FROM dual; returns 5. This article explains how LENGTH works, its variations, and common pitfalls.
What Does LENGTH Do Exactly?
LENGTH returns an integer representing the number of characters in a given string expression. It counts characters, not bytes, by default. If the input string is NULL, the function returns NULL—not zero. An empty string ('') is treated as NULL in Oracle, so LENGTH('') also returns NULL.
The basic syntax is:
LENGTH(char)– wherecharcan be a string literal, a column, or an expression.
For example, to get the length of a column last_name from a table employees:
SELECT last_name, LENGTH(last_name) AS name_length FROM employees;
This returns each last name and its character count.
Character vs. Byte Semantics
Oracle has two ways to measure string length: characters and bytes. The default depends on the database character set. If the database uses a single-byte character set (like US7ASCII or WE8MSWIN1252), character length and byte length are the same for most characters. But with multibyte character sets (like AL32UTF8), a single character may take multiple bytes.
To get the byte length, use LENGTHB. For example:
SELECT LENGTH('é') AS char_len, LENGTHB('é') AS byte_len FROM dual;
In a UTF8 database, this might return char_len = 1 and byte_len = 2 because the accented ‘é’ is stored as two bytes. Always consider which measurement your application needs—character length is usually what users expect, but byte length matters for storage limits.
Variations: LENGTHC, LENGTH2, LENGTH4
Oracle provides several variants that differ in how they count characters:
LENGTHC– counts characters using the complete character set definition (default behavior).LENGTH2– counts characters using UCS-2 code points (for Unicode).LENGTH4– counts characters using UCS-4 code points.
These are rarely needed in everyday SQL, but they exist for specialized Unicode handling. For most purposes, LENGTH is sufficient.
Using LENGTH in WHERE Clauses and Queries
You can use LENGTH anywhere an expression is allowed, such as in the WHERE clause to filter rows based on string length. For example, find all employees with a last name longer than 10 characters:
SELECT last_name FROM employees WHERE LENGTH(last_name) > 10;
You can also use it in ORDER BY to sort by length, or in GROUP BY to group by length. For instance, count how many names have each length:
SELECT LENGTH(first_name) AS len, COUNT(*) FROM employees GROUP BY LENGTH(first_name) ORDER BY len;
This query groups employees by the length of their first name and counts them.
Common Pitfalls and Misconceptions
One common mistake is expecting LENGTH to count bytes by default. As noted, it counts characters. If you need bytes, use LENGTHB.
Another pitfall: LENGTH returns NULL for NULL input, which can surprise people who expect 0. If you want 0 for NULL, use COALESCE(LENGTH(column), 0).
Also, trailing spaces matter. Oracle’s VARCHAR2 stores trailing spaces, and LENGTH counts them. For example, LENGTH('ABC ') returns 4, not 3. If you need to ignore trailing spaces, use LENGTH(TRIM(column)).
Comparing LENGTH with Other Functions
Sometimes you need more than just length. Here are related functions:
INSTR– finds the position of a substring.SUBSTR– extracts a substring.REPLACE– replaces characters.TRIM– removes leading/trailing spaces.
For example, to get the last three characters of a string, you can combine LENGTH with SUBSTR:
SELECT SUBSTR(column, LENGTH(column) - 2) FROM table;
This works because SUBSTR with a negative start position counts from the end, but using LENGTH is clearer for many developers.
Performance Considerations
Using LENGTH in a WHERE clause can prevent index usage if the column is indexed. For example, WHERE LENGTH(last_name) > 10 won’t use a standard B-tree index on last_name because the function must be applied to every row. If you frequently filter by length, consider adding a virtual column with the length precomputed and indexed:
ALTER TABLE employees ADD (last_name_len AS (LENGTH(last_name)));
Then create an index on that virtual column. This can speed up queries that filter on length.
For large tables, avoid calling LENGTH repeatedly in a SELECT list if you don’t need it for every row—only select it when necessary.
Frequently Asked Questions
Does LENGTH count spaces?
Yes, LENGTH counts every character, including spaces, tabs, and punctuation. If you store a string with trailing spaces, they are counted. To ignore leading and trailing spaces, use TRIM first.
What is the difference between LENGTH and LENGTHB?
LENGTH returns the number of characters, while LENGTHB returns the number of bytes. In a single-byte character set they are identical, but in multibyte sets (like UTF-8) they differ. For example, the character ‘ñ’ is one character but may be two bytes.
Why does LENGTH return NULL for an empty string?
In Oracle, an empty string '' is treated as NULL. Since LENGTH(NULL) is NULL, LENGTH('') also returns NULL. If you need 0, use COALESCE(LENGTH(col), 0).
Can I use LENGTH with CLOB or BLOB?
Yes, LENGTH works with CLOB (character large objects) and returns the character length. For BLOB, use DBMS_LOB.GETLENGTH instead, because LENGTH is not designed for binary data.
How do I find the longest string in a column?
You can use MAX(LENGTH(column)) to get the maximum length, and then retrieve the actual string by ordering by length and limiting to one row, or by using a subquery with WHERE LENGTH(column) = (SELECT MAX(LENGTH(column)) FROM table).
Is LENGTH case-sensitive?
No, LENGTH does not care about case; it simply counts characters. Case sensitivity only matters when comparing strings with = or LIKE, not for measuring length.
Related
- SOLIDWORKS Split Command: How to Divide a Part File
- JavaScript String to Int: parseInt, Number, and More
- JavaScript String Objects: What They Are and How They Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Checking Odd Even IntegersThe quickest way to check if a number in cell A1 is odd or even in Excel is…
- How to Add a Sequence of Dates in Excel: 3 Easy WaysTo add a sequence of dates in Excel, enter a starting date and the next date in two…
- Enum Formula in Excel: Syntax, Examples, and AlternativesExcel has no built-in ENUM formula or worksheet keyword. To enumerate values, use a function such as SEQUENCE…
- Excel Split Function: Using TEXTSPLIT to Separate TextIf you mean the split function in Excel, use TEXTSPLIT in Microsoft 365 or Excel 2024 to divide…
- Excel Formula for Adding Calendar Days: Simple StepsThe simplest Excel formula for adding calendar days to a date is =A2+7 (to add 7 days) or…
- ROUNDUP Excel Cell Reference: 10 Useful Formula PatternsWith 12.31 in A2, enter =ROUNDUP(A2,0) to return 13; the formula reads the number from a cell and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.