A text field that works with a short English sample may behave differently when it contains names and descriptions from other languages. One common cause is treating character counts and byte counts as interchangeable.
Oracle provides separate functions for these measurements. Choosing between them begins with the requirement: is the application extracting a number of characters, or working with a byte-oriented format?
Name the unit in the requirement
“Take the first twenty” is incomplete. Twenty characters and twenty bytes can describe different amounts of text.
Oracle documents that SUBSTR measures positions and lengths using characters as defined by the input character set. SUBSTRB uses bytes. Other variants, including SUBSTRC, SUBSTR2, and SUBSTR4, have their own character or code-point semantics.
For an ordinary character-based extraction, a query might use:
SELECT SUBSTR(product_name, 1, 20) AS short_name
FROM products;The intended rule should still be written in the application specification. A developer maintaining the query later needs to know why twenty was selected and what the result is used for.
Learn the position rules
Oracle’s substring functions use positions that may surprise developers arriving from languages with zero-based indexing.
A positive position counts from the beginning. A negative position counts backward from the end. A position of zero is treated as one. Omitting the length returns the remaining portion, while a length below one returns null.
For a simple illustrative value:
SELECT
SUBSTR('REPORT-4827', 1, 6) AS prefix,
SUBSTR('REPORT-4827', -4) AS suffix
FROM dual;The expected outputs are REPORT and 4827.
This is suitable only if the identifier’s structure is actually guaranteed. If identifiers can change format, slicing at fixed positions may conceal a more fundamental parsing problem.
Measure before troubleshooting
Oracle’s LENGTH and LENGTHB functions expose the same character-versus-byte distinction. Comparing them can help investigate a text value whose storage behavior differs from expectations. The documentation also identifies data-type restrictions, including restrictions on LENGTHB with multibyte LOB data.
For an ordinary character column, an inspection query can display both measurements:
SELECT
product_name,
LENGTH(product_name) AS character_count,
LENGTHB(product_name) AS byte_count
FROM products;Choose a few representative records instead of beginning with an entire production dataset. Include the languages and symbols that the application is expected to accept.
Record the column type and database character set alongside the result. That context makes the investigation reproducible.
Keep extraction separate from presentation
A database substring rule does not automatically produce a good interface label. A shortened name may become ambiguous, remove a distinguishing suffix, or stop at an awkward point.
For a product list, consider displaying the full name on the detail page and making truncation visible in the list. For an export, document whether shortening is allowed at all.
Test both the extracted value and its downstream use. A result can satisfy a length requirement while becoming unsuitable as an identifier or misleading as a description.
The strongest design makes the unit explicit, uses the matching function, and preserves the original text whenever the shortened value is only a presentation convenience.
Sources: