Oracle Database does not provide a built-in RIGHT() string function. To return the last characters of a string, use SUBSTR(value, -count); for example, SUBSTR('ORACLE', -3) returns ALE. The negative start position tells Oracle to count backward from the end.
Why Oracle Has No RIGHT() Function
Many databases offer a function named RIGHT that takes a string and a number of characters to return from its right-hand end. Oracle SQL uses a different approach: SUBSTR can start at a position counted from the end, so a separate right-side function is unnecessary. Calling RIGHT(column_name, 3) in ordinary Oracle SQL will generally produce an error because Oracle does not recognize that function as a built-in.
This difference matters when you move a query between database systems. A query written for SQL Server, MySQL, or another system that supports RIGHT may need a small change for Oracle. The equivalent expression for a positive character count is usually SUBSTR(column_name, -count). It is not a special Oracle mode or an optional setting; it is the normal Oracle SQL way to select trailing characters.
- Use
SUBSTRwhen you need a substring beginning at a position counted from the end. - Use
SUBSTRBonly when the requirement is specifically about bytes rather than characters. - Check how the source database treats zero or negative counts before translating an expression; edge-case behavior can differ.
SUBSTR Syntax and the Negative-Position Rule
The general syntax is SUBSTR(character_expression, start_position [, substring_length]). The first argument is the text to examine. The second gives the starting position, and the optional third argument limits how many characters are returned. A positive start position counts from the beginning; a negative one counts backward from the end.
For a right-side extraction, leaving out the length argument is convenient: start at the requested number of characters from the end and return the rest of the string. For example:
SELECT SUBSTR('ORACLE', -3) AS ending
FROM dual;
The result is ALE. With a start position of -1, the result is the final character, E; with -2, it is LE. The Oracle DUAL table is a one-row table often used for evaluating expressions that do not need a source table. On a real table, replace the literal with a column:
SELECT order_code,
SUBSTR(order_code, -4) AS final_four
FROM orders;
If order_code contains ABCD1234, final_four is 1234. If a string is shorter than the requested count, Oracle returns the whole string rather than padding it to the requested width. Thus, asking for the last four characters of Q7 returns Q7.
Using a Count That Changes by Row
The character count does not have to be a literal. It can come from a column or an expression, which is useful when different rows require different suffix lengths. For instance, with a text column named serial_code and a numeric column named chars_to_keep, the expression can be written as:
SELECT serial_code,
chars_to_keep,
SUBSTR(serial_code, -chars_to_keep) AS suffix
FROM device_records;
If one row has serial_code equal to ZXCV9876 and chars_to_keep equal to 4, the suffix is 9876. For a count greater than the string length, the expression returns the available characters. When the count comes from user input or imported data, validate that it is a sensible positive integer rather than assuming every value is safe.
A subtle case is a count of zero. Oracle treats a start position of zero as position one, so SUBSTR('ORACLE', 0) returns the string from the beginning; it does not mean “return zero characters.” Also, when a substring length is zero, SUBSTR returns NULL. Oracle treats a zero-length character string as NULL, so it cannot return a distinct empty string value in the usual VARCHAR2 sense. Decide what zero should mean in the application and handle it explicitly, rather than relying on a right-side function’s behavior in another database.
How Oracle Counts Characters
For normal character-based work, SUBSTR counts characters according to the input character set, not the number of bytes used to store them. That distinction matters with multibyte encodings: a visible character may take more than one byte. If the goal is “the last four characters,” SUBSTR is generally the appropriate choice.
Oracle also provides related functions for different counting rules. SUBSTRB counts bytes. SUBSTRC counts Unicode complete characters, while SUBSTR2 and SUBSTR4 use UCS2 and UCS4 code points, respectively. These alternatives address particular character-set or storage requirements; they are not interchangeable merely because they have similar names. Avoid using a byte-based function for a user-visible character count unless the data format explicitly defines the suffix in bytes.
The data type can affect what you see as well. A CHAR column is fixed-width and is padded with spaces to its declared length. Extracting characters from such a value can therefore include padding spaces at the end. If the source is fixed-width and the intended value excludes padding, consider whether trimming is appropriate, for example with RTRIM. Do not trim automatically if trailing spaces are meaningful in the data. For large character objects such as CLOBs, use the character-object behavior supported by Oracle and check the result type and application needs before placing the expression in a larger operation.
Common Problems and Edge Cases
Most mistakes come from assuming that Oracle’s start-position rules match a dedicated RIGHT function exactly. Keep these cases in mind when writing or translating a query:
- Zero start position: Oracle treats it as position one. It does not select zero characters.
- Zero substring length: Oracle returns
NULL, not a separate empty-string value. - Negative substring length: Oracle returns
NULL. This may not match how another database handles a negative right-side count. - Count larger than the value: The available string is returned; Oracle does not add padding.
- Null input or count: A null argument normally leads to a null result for this expression.
- Trailing spaces: They are characters too, and fixed-width
CHARpadding can make them easy to overlook.
There is another easy-to-miss distinction: SUBSTR(text, -n) and SUBSTR(text, -n, n) are not identical for every input. With a positive integer n, both are commonly used to obtain the final n characters. The version with an explicit length can make the intended count clearer, but its behavior for zero is governed by the zero-length rule above. Test boundary values such as zero, one, a value longer than the text, and null when translating production logic.
Filtering, Indexes, and Query Design
Returning a suffix in a SELECT list is straightforward, but using the expression in a filter has query-planning implications. For example, a condition such as WHERE SUBSTR(account_code, -4) = '1234' applies a function to the column. A conventional index on account_code may not be usable for that predicate in the same way as a direct equality condition, because the database is being asked to compare a transformed value.
If suffix filtering is frequent and the table is large, review the execution plan and consider whether a function-based index or a different data design is suitable. Oracle supports function-based indexes, but the index expression, query expression, data distribution, and optimizer choices all matter; adding one is not a guarantee of a faster query. A query that searches by a stable identifier or a separately stored suffix may be clearer in some systems. Measure with representative data and confirm the plan rather than assuming that SUBSTR is either always slow or always index-friendly.
Also consider whether the suffix is truly the right search key. If codes have a defined structure, storing or validating that structure at data-entry time can prevent inconsistent lengths and unexpected spaces. Keep the original full value when it is needed for display or auditing, and document what the extracted segment represents.
Alternatives and Database Portability
For ordinary character extraction, SUBSTR is the direct built-in alternative to RIGHT in Oracle. REGEXP_SUBSTR can extract text matching a regular expression, but it is usually unnecessary for a fixed number of trailing characters and makes a simple operation harder to read. Use a regular expression when the requirement is a pattern, not just a position-based suffix.
Syntax in other database products is not universal. SQL Server and MySQL provide a RIGHT function, while Oracle SQL uses the negative-position form of SUBSTR. PostgreSQL also has a right function, though details such as invalid lengths and null or empty-string behavior should be checked against the target system. When porting SQL, translate the intent and test boundary cases rather than replacing function names mechanically.
Frequently Asked Questions
What is the Oracle equivalent of RIGHT(string, n)?
For a positive count, use SUBSTR(string, -n). For example, SUBSTR('DATABASE', -4) returns BASE. If the count can be zero, negative, or null, define the desired behavior and handle those cases deliberately.
Does Oracle support RIGHT() in SQL?
Oracle SQL does not provide a built-in RIGHT() function for ordinary string extraction. Use SUBSTR with a negative start position instead. A separately defined custom function could have that name in a particular schema, but it is not Oracle’s standard built-in equivalent.
What does SUBSTR(value, -1) return?
It returns the final character of the value. For example, SUBSTR('ORACLE', -1) returns E. If the input is null or an empty Oracle character string, the result is null.
What happens if the requested count is longer than the string?
Oracle returns the characters that are available. For example, SUBSTR('CAT', -5) returns CAT, not a five-character padded result.
Should I use SUBSTR or SUBSTRB?
Use SUBSTR when the requirement is expressed in characters, which is typical for labels and text. Use SUBSTRB only when the specification is explicitly byte-based, such as a format defined by byte positions. Multibyte characters can make those results different.
Related
- JavaScript split() Syntax, Examples, and Pitfalls
- F-String in Java: Formatting and Alternatives
- SolidWorks: Split One Part Into Two Bodies
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Compare Two Columns in Excel With a FormulaTo compare two Excel columns row by row, enter =IF(A2=B2,"Match","Different") in C2, then fill the formula down. This…
- XLOOKUP Excel Criteria Match: Exact and Multiple CriteriaYes. XLOOKUP can match a criterion exactly by default, and it can also search with approximate or wildcard…
- Excel Date Comparison: Formulas, Rules, and FixesWith dates in A2 and B2, compare them using =A2<B2: Excel returns TRUE if A2 is earlier than…
- How to Clear an A to Z Filter in Excel and Show All RowsTo show all rows hidden by a filter in Excel for Microsoft 365 on Windows, open the filtered…
- BMI Formula in Excel: Calculate and Interpret ResultsTo calculate BMI in Excel, divide weight in kilograms by height in metres squared. If weight is in…
- INDEX MATCH Examples: Exact, Two-Way, and Flexible LookupsWith product names in A2:A6, prices in B2:B6, and the product to find in E2, use =INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)). If…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.