Oracle PL/SQL String Functions: A Practical Guide

Formulas & functions · Updated November 22, 2026

Oracle PL/SQL string functions are built-in routines for measuring, searching, extracting, combining, and changing text. Common choices include LENGTH, SUBSTR, INSTR, REPLACE, TRIM, UPPER, and regular-expression functions; they can be used in PL/SQL expressions and, where appropriate, in SQL statements.

How Oracle string functions fit into PL/SQL

PL/SQL does not have a separate, unrelated set of ordinary string functions. Most of the functions developers use are Oracle SQL built-ins that PL/SQL can call in assignments, conditions, and expressions. For example, v_name := UPPER(TRIM(v_name)); removes leading and trailing spaces and converts the remaining text to uppercase. The same expression can appear in a SQL query, subject to SQL rules and the data types involved.

PL/SQL commonly stores text in VARCHAR2 or fixed-width CHAR variables. Oracle also supports national character types such as NVARCHAR2. When declaring a variable or column, decide whether its length limit should be measured in bytes or characters. A declaration such as VARCHAR2(30 CHAR) expresses a character-length limit, while byte semantics can impose a different practical limit when text contains multibyte characters.

A key Oracle behavior is that a zero-length character string, written as '', is currently treated as NULL for VARCHAR2 values. This affects checks, defaults, and concatenation. It is not safe to assume that an empty string and a null value remain distinct as they might in another database system.

Measure text with LENGTH and related functions

LENGTH(text) returns the length of a character value. For example, LENGTH('Hello') returns 5. This is useful for validation, such as rejecting a code that exceeds a specified character count, or for deciding whether a value is blank after trimming. For a value that might be null, remember that the result can also be null; use an explicit null check or a suitable default if your logic requires a number.

Oracle offers several related functions for different definitions of length:

  • LENGTH measures characters according to the input character set.
  • LENGTHB measures bytes, which can differ from character count for multibyte text.
  • LENGTHC, LENGTH2, and LENGTH4 measure using Unicode-oriented character or code-unit definitions.

Choose the measure that matches the requirement. A user-facing limit such as 30 displayed characters usually calls for character-aware handling, not a byte count. A storage or protocol limit stated in bytes may require LENGTHB. These measures are not interchangeable for every character set or data type, and some functions have restrictions with large-object values. Check the Oracle documentation for the database version and input type in use when working with CLOBs or national character data.

Find and extract text with INSTR and SUBSTR

INSTR locates a substring and returns its starting position, or 0 when it is not found. Its general form is INSTR(source, search, position, occurrence). The position and occurrence arguments are optional. For instance, INSTR('BANANA', 'AN', 1, 2) returns 4, the start of the second occurrence of AN. This is useful for locating delimiters, identifying a file extension, or finding the second separator in a structured value.

SUBSTR returns part of a string. Its form is SUBSTR(source, start, length), with the length optional. In SUBSTR('ORACLE', 2, 3), the result is RAC. A start position of 0 is treated as 1; a negative start counts backward from the end of the input. Those rules are convenient, but they can surprise code that expects zero-based indexing from languages such as Java or Python.

Search and extraction often work together. To get the part of a value before the first slash, find the slash with INSTR and pass that position to SUBSTR. Guard the case where the delimiter is absent: because INSTR returns 0 when it cannot find a match, blindly using that result in an extraction can produce an unintended result. Decide whether missing delimiters should return the whole value, return null, or raise an application error.

Change case, remove padding, and replace characters

UPPER, LOWER, and INITCAP change letter case. They are often used to normalize input before comparing it or to format labels for display. Case conversion can depend on language and character-set rules, so it should not be treated as a universal substitute for a carefully defined comparison policy. For identifiers that must be compared consistently across systems, document the normalization rule and test it with the languages and characters your data contains.

TRIM removes a chosen character from one or both ends of a value. For example, TRIM(BOTH 'x' FROM 'xxxtextxx') returns text. If no trim character is supplied, TRIM removes spaces. LTRIM and RTRIM remove characters from the left or right end. Their second argument is a set of characters to remove, not a complete substring to match. Thus, trimming the characters in 'xy' can remove any run of either character from the edge.

REPLACE(source, search, replacement) replaces occurrences of one string with another. For example, REPLACE('1-2-3', '-', '/') returns 1/2/3. If the replacement argument is omitted or null, matching text is removed. TRANSLATE performs character-by-character mapping instead: each character in its source map is replaced by the character in the same position in its destination map. Use REPLACE for a multi-character pattern and TRANSLATE for a set of individual character substitutions.

Join strings and add padding

Use the concatenation operator || to join text. For example, first_name || ' ' || last_name combines two names with a space. Oracle also provides CONCAT, which takes two arguments, so the operator is usually clearer when combining several pieces. Oracle treats a null or zero-length character value differently from systems where concatenating with null always makes the entire result null: a non-null operand can remain as the result. If both operands are null, the result is null.

LPAD and RPAD add padding on the left or right to reach a requested length. For example, LPAD('42', 5, '0') returns 00042, which can be useful for fixed-width display codes. Padding is primarily a formatting operation; it does not change the underlying numeric meaning of a value. Be deliberate about the requested length, because text longer than the target length may be truncated to fit. For identifiers that must never be shortened, validate the input length before padding.

Use regular expressions for pattern-based work

When a simple search or replacement is not expressive enough, Oracle provides regular-expression functions. REGEXP_LIKE tests whether text matches a pattern and is commonly used in a WHERE clause or PL/SQL condition. REGEXP_SUBSTR extracts a matching portion, REGEXP_INSTR finds a match position, REGEXP_REPLACE replaces matched text, and REGEXP_COUNT counts matches. These functions are useful for patterns such as repeated whitespace, a group of digits, or a structured token.

For instance, REGEXP_REPLACE(value, '[[:space:]]+', ' ') replaces each run of whitespace with a single space. A regular expression can express a broader pattern than REPLACE, but it is usually harder to read and may require more processing. Use the simplest function that clearly expresses the requirement. Regular expressions are also not a complete validation system for complex formats: a pattern that checks the shape of an email address or identifier may not establish that the value is genuinely valid for the application.

Use string functions safely in PL/SQL code

String functions can be used directly in a PL/SQL block, but the result must fit the destination variable and be handled according to its nullability and type. A declared variable with a short size can raise a value error if an assigned result is too long. When a transformation can expand text, such as padding or replacement, allow enough room or validate the result before assigning it.

Consider a routine that receives a user-entered code. It might trim outside spaces, normalize case, check a length range, and then search for a separator. Keep each rule explicit rather than combining everything into one complicated expression. That makes it easier to report whether the input was blank, too long, or missing a required delimiter. If a value is used in a SQL query, bind it as a parameter rather than assembling SQL text with concatenation; string functions do not prevent SQL injection when untrusted input is inserted into dynamic SQL.

Also distinguish SQL expressions from procedural control flow. A condition such as IF v_text IS NULL THEN checks for null directly; comparing a value with = NULL does not work as an ordinary equality test. Oracle treats an empty string as null in this context, so a check for null also catches a zero-length VARCHAR2 value. A string containing only spaces is not null until it is trimmed or otherwise normalized.

Choose the simplest function for the job

Function choice should follow the shape of the problem, not just familiarity. A small set of rules helps keep PL/SQL readable and predictable:

  • Use LENGTH for character length and LENGTHB only when the requirement is explicitly about bytes.
  • Use INSTR and SUBSTR for known delimiters and fixed-position extraction.
  • Use REPLACE for literal substring replacement and TRANSLATE for character-by-character mapping.
  • Use TRIM, LTRIM, or RTRIM for edge cleanup; they do not remove matching text from the middle.
  • Use regular expressions when the rule is genuinely pattern-based, and keep the pattern documented and tested.

There is no single best function for every text operation. The right choice depends on whether the input is ordinary text, a byte-limited value, a large object, or a value with a defined format. Test boundary cases as well as normal examples: null input, an empty string, a missing delimiter, a delimiter at the start or end, multibyte characters, and text already longer than a padding target.

Frequently Asked Questions

What is the difference between SUBSTR and INSTR?
SUBSTR returns part of a string based on a starting position and optional length. INSTR returns the position where a searched substring occurs, or 0 when it is not found. They are often combined to locate and then extract text.

Does Oracle treat an empty string as NULL?
For Oracle character values such as VARCHAR2, a zero-length string written as '' is treated as null. A string containing spaces is not the same value until those spaces are removed, for example with TRIM.

Should I use LENGTH or LENGTHB?
Use LENGTH when the requirement concerns character length. Use LENGTHB when it specifically concerns bytes, such as a byte-oriented limit. Multibyte characters can make the two results differ.

How do I remove every occurrence of a character?
For a literal character or substring, use REPLACE(source, search, ''). Oracle treats the empty replacement as null, which makes matching text be removed. If you need to remove characters that match a pattern, consider REGEXP_REPLACE.

Can I call SQL string functions inside PL/SQL?
Yes. Common SQL string functions such as UPPER, SUBSTR, and INSTR can be used in PL/SQL expressions. Check the function’s supported input types and the rules of the particular SQL statement or PL/SQL context where it is used.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.