Oracle SQL does not provide a built-in LEFT() string function. To return the first characters of a value, use SUBSTR(value, 1, length); for example, SUBSTR('Oracle Database', 1, 6) returns Oracle.
Does Oracle SQL have a LEFT function?
Developers moving from SQL Server, MySQL, or other database systems often look for LEFT(text, number_of_characters). Oracle’s usual equivalent is SUBSTR, which extracts characters from a string using a starting position and, optionally, a length. The first position is 1, so starting at 1 selects from the beginning.
This is a SQL function, not a special command for a particular Oracle client. You can use it in a SELECT statement, in expressions such as WHERE and ORDER BY, and in PL/SQL expressions. The same basic syntax applies across commonly used Oracle Database versions. For a straightforward prefix, SUBSTR is clearer than using a regular expression or writing a custom function.
SUBSTR syntax and how positions work
The general form is SUBSTR(char, position [, substring_length]). The square brackets in this description mean the length argument is optional; do not type the brackets into a query. char is the source string, position is where extraction begins, and substring_length is how many characters to return.
SUBSTR(text, 1, n)returns up to the firstncharacters.SUBSTR(text, 1)returns the string from its first character to its end.SUBSTR(text, -n)startsncharacters from the end and returns the remainder.
Oracle treats a starting position of 0 as 1. A negative starting position counts backward from the end of the string: for instance, SUBSTR('DATABASE', -4) returns BASE. That behavior can be useful, but it is not needed to get a left-hand prefix. For that task, use a starting position of 1.
Basic examples with strings and table columns
For a quick test, you can select a string literal from Oracle’s one-row DUAL table:
SELECT SUBSTR('Oracle Database', 1, 6) AS prefix FROM dual;
The result is Oracle. In application queries, the first argument is usually a column rather than a literal. If a table called products has a product_code column, this expression returns the first three characters for each row:
SELECT product_code, SUBSTR(product_code, 1, 3) AS code_prefix FROM products;
For example, a value of ABX-204 produces ABX. The source value is not changed: the expression creates a result for the query. To store a shortened value, you would need a separate data-changing operation, such as an UPDATE; extracting a prefix in a SELECT does not edit the table.
If the desired prefix length is fixed, put that number directly in the expression. If the length varies, it can come from another column or a bind variable, as explained below.
Using a variable or column for the prefix length
The third argument does not have to be a number typed directly into the SQL statement. It can be a numeric column or an input bind variable. For example, if each row in items has a label and a numeric prefix_size, you can write:
SELECT SUBSTR(label, 1, prefix_size) AS short_label FROM items;
For a query whose length is supplied by an application, a bind placeholder can be used: SELECT SUBSTR(product_code, 1, :prefix_len) FROM products;. The application or database client supplies the value for :prefix_len. Bind variables are preferable to assembling user input into SQL text because they separate the value from the statement and help prevent SQL injection. The exact interface for setting a bind variable depends on the client or programming language.
Consider the input when the length can vary. A positive length returns that many characters, subject to the string’s available length. A length larger than the string does not add padding; Oracle returns the characters that are present. A length of zero or less returns NULL, so validate user-supplied lengths if a nonempty result is required.
NULL values, empty strings, and other edge cases
Several behaviors matter when a prefix expression is used on real data:
- If the source value is
NULL, the result isNULL. - If the requested length is zero or negative,
SUBSTRreturnsNULL. - If the requested length is greater than the source length, the result is simply the available string; there are no extra spaces.
- In Oracle SQL, a zero-length character string such as
''is treated asNULL. Therefore, extracting a prefix from an empty string also gives a null result.
These details can affect filters and reports. For example, SUBSTR(code, 1, 3) = 'ABC' does not match rows where code is null; comparisons with null are not true. If nulls need to be handled explicitly, use a condition such as code IS NULL or an appropriate NVL or COALESCE expression, depending on the intended result. Do not replace null with an arbitrary placeholder unless that value has a defined meaning in the data.
Also distinguish a variable-length value such as VARCHAR2 from a fixed-width CHAR column. A CHAR value is blank-padded to its declared width, so extracting a prefix that reaches those padded positions can include trailing spaces. If spaces matter, check the column type and use trimming only when removing them matches the data rules.
Characters, bytes, and multilingual text
For ordinary text, use SUBSTR: its position and length are based on characters rather than raw bytes. This distinction matters in a multibyte character set, where a character can occupy more than one byte. A request for the first four characters is not necessarily a request for the first four bytes.
Oracle also provides related functions for specific units. SUBSTRB measures positions and lengths in bytes. Other variants, including SUBSTRC, SUBSTR2, and SUBSTR4, use particular Unicode character or code-point definitions. These alternatives are useful only when the application’s requirements specifically refer to those units. For names, labels, and most other user-facing text, the plain character-based SUBSTR is generally the appropriate starting point.
If the string can contain complex Unicode characters, such as a visible symbol formed from multiple code points, “character” can have more than one technical interpretation. Test the chosen function with representative data and confirm that its unit of measurement matches the application requirement. Byte-based extraction is especially unsuitable as a casual replacement for character-based prefix logic.
Finding rows by prefix and considering indexes
To return rows whose first three characters equal a known code, a direct expression is:
SELECT product_code FROM products WHERE SUBSTR(product_code, 1, 3) = 'ABC';
For a simple literal prefix, a pattern condition may express the same intent more directly: WHERE product_code LIKE 'ABC%'. The percent sign means any sequence of following characters, including none. If the prefix itself contains pattern characters such as % or _, remember that LIKE treats them as wildcards unless they are escaped using an appropriate ESCAPE clause. A SUBSTR equality test does not interpret those characters as wildcards.
Performance depends on the query, indexes, data types, and optimizer decisions. Applying SUBSTR to a column in a predicate can make an ordinary index on that column less directly useful. A prefix LIKE condition may allow index use in suitable circumstances, but it is not guaranteed. If this filter is important on a large table, inspect the execution plan and consider whether a function-based index on the same expression is appropriate. Such an index adds storage and requires maintenance as data changes, so its value depends on the workload.
Translating LEFT expressions from other databases
When porting a query, the common translation is mechanical: replace LEFT(column, n) with SUBSTR(column, 1, n). For example, a SQL Server expression LEFT(customer_name, 5) becomes SUBSTR(customer_name, 1, 5) in Oracle SQL. Check the source system’s behavior for nulls, empty strings, and invalid lengths rather than assuming every detail is identical.
Choose a different Oracle function when the task is different. Use INSTR to locate a character or substring, and use SUBSTR with that position when you need text around a delimiter. Use REGEXP_SUBSTR when the extraction rule genuinely depends on a regular-expression pattern. For a simple first-N-characters operation, those approaches add complexity without improving clarity.
Frequently Asked Questions
What is the Oracle equivalent of LEFT(string, n)?
Use SUBSTR(string, 1, n). The start position is 1, and the third argument specifies the maximum number of characters to return.
Does SUBSTR start counting at zero or one?
Oracle positions normally start at 1. A starting position of 0 is treated as 1, but writing 1 makes the intended beginning of the string explicit.
What happens if the requested length is longer than the string?
Oracle returns the characters available in the string; it does not pad the result to the requested length. A zero or negative length, by contrast, produces NULL.
How do I get the last characters instead?
Use a negative starting position. For example, SUBSTR('DATABASE', -4) returns BASE. This counts backward from the end rather than taking a prefix.
Can I use SUBSTR in a WHERE clause?
Yes. For example, WHERE SUBSTR(product_code, 1, 3) = 'ABC' filters for values whose first three characters are ABC. For large tables, compare the execution plan with a suitable prefix LIKE condition and review index options.
Related
- Snowflake SELECT SPLIT Example: Parse Delimited Strings
- Split Data in R for Training and Testing: A Practical Guide
- F-Strings in JavaScript: Use Template Literals Instead
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SORT and INDEX: How to Sort Data and Return ValuesTo sort a range in Excel for Microsoft 365, use =SORT(A2:C6,3,-1) to sort its rows by the third…
- Excel Sum of Filtered Data: SUBTOTAL, AGGREGATE & FILTERTo sum only the rows currently visible after filtering in Excel, put =SUBTOTAL(9,C2:C7) in a cell outside the…
- Add a Sort List in Excel: Create a Custom OrderTo add a custom sort list in Excel for Microsoft 365 on Windows, go to File > Options…
- Excel Nested INDEX MATCH: How to Use ItTo create a nested INDEX MATCH in Excel, you use one INDEX MATCH formula as the lookup value…
- How to Create a Filter in Excel: Step-by-Step GuideTo create a filter in Excel for Microsoft 365 on Windows, click a cell in your data range,…
- Excel MID Function: Extract Text from the Middle of a CellExcel’s MID function extracts a chosen number of characters from the middle of a text string. Its syntax…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.