PL/SQL String Functions: Syntax, Examples, and Uses

Formulas & functions · Updated November 22, 2026

PL/SQL string functions let you measure, search, extract, change, and format character data; commonly used examples include LENGTH, SUBSTR, INSTR, TRIM, REPLACE, and UPPER. Call them in PL/SQL expressions just as you would in SQL, choosing each function according to whether you need character positions, byte positions, exact text replacement, or pattern matching.

What string functions do in PL/SQL

PL/SQL is Oracle Database’s procedural language, and its string-handling features work with character values such as VARCHAR2, CHAR, NCHAR, and NVARCHAR2. Large text may instead be stored in a character large object, or CLOB. String functions can be used when assigning variables, building messages, checking input, and processing values returned by SQL statements.

Most familiar string operations are built-in functions, not special PL/SQL statements. For example, this assignment extracts six characters from a variable:

l_part := SUBSTR(l_text, 8, 6);

The same function can appear in a SQL query, such as SELECT SUBSTR(column_name, 1, 10) FROM table_name. The surrounding context changes, but the basic purpose and arguments of the function remain the same. PL/SQL also has the concatenation operator ||, which is often more readable than calling CONCAT.

Measure string length correctly

LENGTH returns the number of characters in a value, while LENGTHB returns the number of bytes used to represent it. The difference matters when text contains characters that occupy more than one byte in the database character set. For instance, a string can have three characters but require more than three bytes.

Oracle also provides LENGTHC, LENGTH2, and LENGTH4, which count according to complete Unicode characters, UCS2 code points, and UCS4 code points respectively. These alternatives are useful when an application has a specific Unicode counting requirement; they are not interchangeable with a byte count. In particular, a visual symbol can be formed from more than one Unicode code point, so a count of code points does not always equal the number of symbols a person perceives.

Datatype behavior matters too. A CHAR value is fixed-width and blank-padded, so its stored trailing spaces can contribute to the length. A VARCHAR2 value is variable-width and does not get padded to its declared maximum. When validating a user’s input, decide whether spaces at the end should count before selecting a length check.

For example, if a variable contains Oracle, LENGTH(l_text) returns 6. If you need to enforce a byte-based storage limit rather than a character limit, measure bytes deliberately and account for the database character set.

Extract text with SUBSTR

SUBSTR(char, position, length) returns a portion of a string. The length argument is optional. Positions start at 1; a position of 0 is treated as 1, and a negative position counts backward from the end of the value. If the requested length is less than 1, the result is null.

Here is a complete PL/SQL example. It prints PL/SQL:

DECLARE
  l_text VARCHAR2(100) := 'Oracle PL/SQL';
  l_part VARCHAR2(20);
BEGIN
  l_part := SUBSTR(l_text, 8, 6);
  DBMS_OUTPUT.PUT_LINE(l_part);
END;
/

Oracle has byte-oriented and Unicode-oriented variants, including SUBSTRB, SUBSTRC, SUBSTR2, and SUBSTR4. Use the ordinary SUBSTR for typical character-based work. Choose a variant only when the position and length need to be interpreted in its stated unit; a byte offset can split a multibyte character if used carelessly.

A common use is taking a prefix or suffix, but avoid relying on fixed positions if the input format can vary. If you need to find a delimiter first, combine INSTR with SUBSTR, or consider a regular-expression function for more complex patterns.

Search for text with INSTR

INSTR returns the position where one string occurs inside another. Its general form is INSTR(source, search, start_position, occurrence); the final two arguments are optional. Positions and occurrences start at 1. If the requested occurrence is not found, the function returns 0, making it useful in conditional logic.

For example, INSTR('banana', 'an', 1, 2) returns 4 because the second occurrence of an begins at position 4. A negative start position searches backward from the end. INSTRB, INSTRC, INSTR2, and INSTR4 provide related byte or Unicode counting variants.

Test for a missing delimiter explicitly before using the returned position in a substring calculation. If a comma is absent, an expression that assumes the comma exists may produce an unexpected portion of the input or an invalid length. For example, a PL/SQL condition such as IF INSTR(l_text, ',') = 0 THEN can route malformed input to a suitable error-handling path.

Change case, remove characters, and replace text

UPPER converts letters to uppercase, LOWER converts them to lowercase, and INITCAP capitalizes the first letter of words according to Oracle’s word-boundary rules. These functions are useful for display formatting and simple normalization. Case conversion can depend on language and character-set rules, so it should not be treated as a universal substitute for a deliberate case-insensitive comparison strategy.

TRIM removes a specified character from the leading edge, trailing edge, or both edges of a value. If no trim character is supplied, it removes spaces. Its general form is TRIM([LEADING | TRAILING | BOTH] [trim_character FROM] source). The older convenience functions LTRIM and RTRIM remove characters from the left or right side.

One easily missed detail: the character argument to LTRIM or RTRIM is treated as a set of characters, not as one exact string to remove. Thus, trimming with 'ab' can remove repeated leading or trailing occurrences of either a or b. Use REPLACE when the goal is to replace an exact substring throughout the value.

REPLACE(source, search, replacement) substitutes matching text; omitting the replacement removes the matched text. TRANSLATE(source, from_characters, to_characters) instead maps individual characters by position. Characters listed in the second argument that have no corresponding character in the third argument are removed. Choose REPLACE for a phrase or token and TRANSLATE for character-by-character conversion.

Join strings and build formatted values

The concatenation operator || joins character values, and it can combine more than two pieces in one expression. For example, l_full_name := l_first_name || ' ' || l_last_name; inserts a space between two name values. The CONCAT function also joins strings, but accepts two arguments, so chained concatenation is usually clearer with ||.

Oracle currently treats a zero-length character string as null. In concatenation, a null or zero-length operand combined with a non-null string generally leaves the other string as the result; concatenating two null values results in null. Do not assume that an empty string is a distinct value from null in Oracle character data. This behavior can affect checks, output formatting, and comparisons, especially when migrating code from another database.

LPAD and RPAD add padding on the left or right to reach a requested length. They can format identifiers or reports, but padding is not a substitute for storing a value in a fixed-width datatype. Check the requested output length and the padding character, since Oracle may truncate the original value when it is longer than the requested result length.

Use regular expressions for patterns

For structured patterns that are awkward to express with a literal search, Oracle supplies regular-expression functions. REGEXP_LIKE tests whether text matches a pattern; REGEXP_SUBSTR extracts a match; REGEXP_INSTR reports a match position; REGEXP_REPLACE replaces matches; and REGEXP_COUNT counts them.

For example, a simple expression that looks for one or more digits can be used to check whether text contains a numeric run. Regular expressions are more expressive than INSTR, but they also make rules harder to read when overused. For a fixed delimiter or literal substring, INSTR and SUBSTR are often easier to maintain. For a pattern with optional elements or repeated character classes, a regular expression may be the more direct tool.

Patterns can have options that affect case sensitivity and matching behavior. Review the exact Oracle regular-expression syntax and the function’s arguments when implementing validation, and test representative inputs including empty values, punctuation, and non-English text. A pattern match should not automatically be treated as proof that a value meets every business rule.

Nulls, character sets, and common mistakes

Oracle’s treatment of null and empty character strings is a frequent source of confusion. A zero-length string assigned to a character variable is treated as null, while a string containing a space is not null. Therefore, TRIM can turn a string made only of spaces into a zero-length result, which Oracle treats as null. Use IS NULL to test nulls; an expression such as value = NULL does not perform a valid null test.

Another common mistake is mixing character counts with byte counts. Use LENGTH and SUBSTR for character-oriented operations in ordinary code, and use their byte-oriented counterparts only when the requirement is genuinely defined in bytes. Likewise, do not assume that ASCII means every character has a conventional ASCII code: Oracle describes it as returning the decimal representation of the first character in the database character set. CHR returns a character for a numeric code in that character set.

Finally, remember that a string function’s result has a datatype and a size. When assigning a result to a constrained variable, consider whether the operation can produce a value too large for the target. With very large text, review the relevant CLOB support and LOB-specific operations, such as those in DBMS_LOB, rather than assuming every operation behaves like a small VARCHAR2.

How to choose the right function

Start with the unit and kind of operation, rather than memorizing a long list of names. These choices cover most routine PL/SQL string work:

  • Measure a value in characters with LENGTH, or in bytes with LENGTHB.
  • Extract a known range with SUBSTR; find a literal occurrence with INSTR.
  • Remove edge spaces or selected edge characters with TRIM, LTRIM, or RTRIM.
  • Change letter case with UPPER, LOWER, or INITCAP.
  • Replace an exact substring with REPLACE, or map individual characters with TRANSLATE.
  • Join values with ||, and use a regular-expression function when the requirement is genuinely pattern-based.

Also decide how the code should handle null input, blank padding, Unicode text, and missing delimiters. Writing those assumptions down makes the chosen function easier to review and helps prevent a valid-looking expression from silently mishandling real data.

Frequently Asked Questions

What is the most commonly used string function in PL/SQL?
SUBSTR, INSTR, LENGTH, TRIM, and REPLACE are all common; there is no single function that is best for every task. Choose by whether you need to extract, locate, measure, clean, or substitute text.

What is the difference between SUBSTR and INSTR?
SUBSTR returns a portion of a string based on a position and length. INSTR searches for text and returns the position where a requested occurrence begins, or 0 when it is not found.

Does LENGTH count spaces in Oracle?
Yes. Spaces inside a value count, and trailing padding in a fixed-width CHAR value can count too. If you intend to ignore edge spaces, apply TRIM before measuring and consider that an all-space result becomes null.

How do I remove a word from a string?
Use REPLACE(source, word, '') to remove matching occurrences of that exact text. Oracle treats the empty replacement as null, and REPLACE removes all matching occurrences rather than only the first.

Can PL/SQL string functions be used in SQL statements?
Many built-in character functions, including SUBSTR, INSTR, and UPPER, can also be used in SQL expressions. Check the documentation for a particular function when its availability or argument rules matter, especially for specialized LOB operations.

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.