Oracle SQL does not provide a built-in LEFT() function. To return the first characters of a string, use SUBSTR(string, 1, number_of_characters); for example, SUBSTR('DATABASE', 1, 4) returns DATA.
Why Oracle Has No LEFT() Function
Many databases offer a function named LEFT that takes a string and a character count. Oracle’s SQL approach is to use the more general SUBSTR function instead. It can return characters from the beginning, middle, or end of a value, so the same function covers the common jobs handled by both LEFT and RIGHT elsewhere.
This difference matters when you copy SQL from a tutorial written for another database. A query such as SELECT LEFT(product_name, 5) FROM products is not valid Oracle SQL and usually raises an error because Oracle cannot find a function named LEFT. Rewrite it as SELECT SUBSTR(product_name, 1, 5) FROM products. The first argument is the source text, the second is the starting position, and the third is the number of characters to return.
Oracle’s syntax is not a limitation on what can be extracted; it is a difference in function names and conventions. Once you know that position 1 means the first character, the equivalent is straightforward.
How SUBSTR Works
The standard form is SUBSTR(char, position, substring_length). The character expression can be a literal, a column, or the result of another expression. The position and length are numeric expressions. The length argument is optional: if it is omitted, Oracle returns the text from the starting position to the end of the source value.
SUBSTR('ORACLE', 1, 2)returnsOR.SUBSTR('ORACLE', 3, 3)returnsACL.SUBSTR('ORACLE', 4)returnsCLE, because no length was supplied.
In the leftmost-character pattern, the starting position stays at 1 and the length controls how many characters are returned. For example, to show the first eight characters of each customer name, use SUBSTR(customer_name, 1, 8). If a value has fewer than eight characters, Oracle returns the characters that are available; the expression does not fail just because the requested length exceeds the value’s length.
A typical query might be SELECT SUBSTR(last_name, 1, 3) AS name_prefix FROM employees. It produces a three-character prefix for each row where last_name contains a value. The alias labels the output column; it does not change the stored name.
Examples for Common Tasks
Use the same pattern for labels, codes, and simple text previews. The count is a maximum length, not a requirement that every result contain exactly that many characters.
- First character:
SUBSTR('Canada', 1, 1)returnsC. - First five characters:
SUBSTR('Customer', 1, 5)returnsCusto. - Text before a known position: if a code is
AB-492,SUBSTR('AB-492', 1, 2)returnsAB. - Prefix from a column:
SUBSTR(order_code, 1, 4)returns the first four characters of each non-nullorder_code.
For a value with a separator, a fixed character count is useful only when the prefix length is consistent. If the separator can move, combine INSTR and SUBSTR instead. For example, to get all text before the first hyphen in a code, use SUBSTR(code, 1, INSTR(code, '-') - 1). If the hyphen is in position 3, the length becomes 2 and the result is the two characters before it. Check what should happen when the separator is absent: INSTR returns 0 when it cannot find the search text, which makes that length negative and can produce an unintended result. Handle that case explicitly if it is possible in your data.
Positions, Lengths, and Edge Cases
Oracle counts character positions from 1, not 0. Position 1 is the first character, position 2 is the second, and so on. Oracle treats a starting position of 0 as 1. A negative starting position counts backward from the end: for example, SUBSTR('ORACLE', -2, 2) returns LE. Negative positions can be useful, but they do not mean “take characters from the left”; use position 1 for that.
The length argument determines the maximum number of characters returned. If it is less than 1, Oracle returns null. If the length is longer than the remaining text, Oracle returns only the available characters. A null source value produces a null result. Oracle also treats a zero-length character string, such as '', as null in SQL, so it is not a distinct empty-string value in the way it is in some other systems.
These rules can explain results that seem surprising when a query is migrated from another database. In particular, do not use a zero-based start position because a language or database API uses one. Oracle SQL’s documented character positions begin at 1, and using 0 does not skip the first character.
SUBSTR, SUBSTRB, and Unicode Text
For ordinary character-based extraction, use SUBSTR. Oracle also provides variants for cases where the unit being counted matters. SUBSTRB counts bytes rather than characters; SUBSTRC works with Unicode complete characters; and SUBSTR2 and SUBSTR4 count UCS2 and UCS4 code points, respectively. These functions are not interchangeable in every character set or for every kind of text.
A character can occupy more than one byte in a multibyte character set. Therefore, a byte-based count may not match what a person sees as a character, and cutting a byte sequence at an arbitrary point can be unsuitable for text handling. If the goal is “the first five visible characters,” begin with SUBSTR(value, 1, 5), not SUBSTRB. Consider the specialized variants only when the application’s encoding requirements call for their specific counting behavior.
Even the word “character” can have subtleties for Unicode text, such as combined marks or emoji sequences that display as one symbol but contain multiple code points. If exact user-perceived character boundaries are important, verify how the database character set and the chosen Oracle function treat the specific text. Do not assume a byte count, code-point count, and displayed-symbol count are the same.
SUBSTR Is Not LTRIM
A common confusion is between taking characters from the left and removing characters on the left. SUBSTR(value, 1, n) keeps the first n characters. LTRIM(value) removes leading blank spaces by default. They perform different operations: trimming does not return a fixed-width prefix, and a substring does not clean up leading spaces.
For example, if a value is ' Maple', a leftmost substring may include the leading spaces, depending on the requested length. LTRIM(' Maple') returns Maple. To trim first and then take a prefix, nest the functions: SUBSTR(LTRIM(value), 1, 4). The trim character set can also be supplied to LTRIM, but that is a separate decision from the number of characters selected.
Similarly, RTRIM removes trailing characters, while TRIM can remove leading or trailing characters according to its syntax. Choose based on the intended transformation: extract a position range with SUBSTR, or remove unwanted characters with a trim function.
Choosing a Pattern and Avoiding Common Errors
For a fixed-length prefix, SUBSTR(column, 1, n) is usually the clearest choice. For a prefix whose length depends on a delimiter, use a delimiter search such as INSTR with appropriate handling for missing delimiters. For removing leading whitespace or selected leading characters, use LTRIM. Keeping these purposes distinct makes SQL easier to review and reduces accidental data changes.
- Using the wrong function name: replace a copied
LEFT(...)call withSUBSTR(..., 1, ...). - Starting at zero: use 1 for the first character; Oracle treats 0 as 1 rather than as a separate position.
- Confusing characters and bytes: use
SUBSTRfor character counts unless byte-based processing is specifically required. - Assuming a fixed length exists: shorter inputs return shorter results, so downstream formatting may need its own padding rule.
- Ignoring nulls: null input yields null output; decide whether a query should preserve null or replace it with a default using a separate expression.
- Assuming delimiters are always present: test or explicitly handle rows without the delimiter before deriving the substring length.
Also remember that extracting a prefix for display is not the same as validating or normalizing the original value. A four-character prefix can collide across different full values. If the prefix is used as a key, join condition, or security-relevant identifier, confirm that truncation cannot create ambiguous matches.
Performance and Use in Queries
SUBSTR is often inexpensive for simple projections, but using a function around a column in a filter can affect how Oracle uses an ordinary index on that column. For example, a predicate comparing SUBSTR(code, 1, 3) to a prefix applies a function to the column. Depending on the query and schema, Oracle may not be able to use a standard index as effectively as it could for a direct range or equality condition.
Do not assume that every such predicate causes a full table scan: the execution plan, available indexes, statistics, and query shape matter. If prefix filtering is performance-critical, inspect the actual execution plan and consider an appropriate index design, such as a function-based index, where it fits the workload and database configuration. Measure with representative data rather than changing a query based only on the presence of SUBSTR.
For a one-off output column or a modest query, clarity should usually come first. For repeated filtering on a derived prefix, data modeling or indexing may be more important than choosing among equivalent string expressions.
Frequently Asked Questions
Does Oracle SQL have a LEFT() function?
No. Use SUBSTR(value, 1, length) to return characters from the left side of a string.
What is the Oracle equivalent of LEFT(name, 3)?
Write SUBSTR(name, 1, 3). This returns up to the first three characters of name.
How do I get the last characters in Oracle?
Use a negative starting position, such as SUBSTR(value, -3, 3) for up to the last three characters. A negative position counts backward from the end.
What happens if the requested length is longer than the text?
Oracle returns the available characters; it does not raise an error just because the source is shorter than the requested length.
Is SUBSTR the same as LTRIM?
No. SUBSTR extracts characters by position. LTRIM removes leading spaces by default, or leading characters from a supplied set.
Should I use SUBSTR or SUBSTRB?
Use SUBSTR when you mean character positions. Use SUBSTRB only when the requirement is explicitly based on bytes, since multibyte text can occupy more than one byte per character.
Related
- Python Logging F-Strings: When to Use Them and Why
- Onshape Split Tool: How to Split Parts and Faces
- Oracle SQL String Split: Methods and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Freeze Values in Excel Formulas: A Step-by-Step GuideTo stop a formula result changing, copy the formula cell or range and paste it back as values:…
- Outlook Folder Rules: Create Rules to Sort EmailCreate an Outlook rule by choosing a condition, such as a sender or subject phrase, and an action…
- INDEX and MATCH in Excel: How to Find Values FlexiblyYes. In Excel, INDEX and MATCH work together to find a value by its position: MATCH locates the…
- Interpolate Between Two Points in Excel With a FormulaTo interpolate between two points in Excel, use a straight-line formula: estimated y = y1 + (target x…
- MATLAB split Function: Syntax and ExamplesMATLAB’s split function separates text into pieces at a delimiter and returns the pieces as a string array.…
- Split Cell Content in Excel: Formulas and ExamplesTo split text from one cell into separate cells in Microsoft 365, use TEXTSPLIT. If A2 contains Maya…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.