Extracting Text with Oracle SUBSTR

The Oracle SUBSTR function returns part of a character value. A query can use it to obtain a prefix, suffix, fixed-width code segment, or text whose boundary was calculated by another expression. It is commonly used for identifiers, imported values, display formatting, and legacy text that contains several logical elements in one string.

SUBSTR does not modify the source column. It returns an extracted value to the SQL statement. Its result depends on the starting position, optional length, source datatype, and the length semantics selected by the particular member of the SUBSTR family. Oracle AI Database 26ai supports character, byte, UCS-2, UCS-4, and Unicode complete-character variants.

SUBSTR Syntax and One-Based Positions

The ordinary character-oriented syntax is:

SUBSTR(source, position [, substring_length])

• source is the character expression from which text is returned.
• position identifies the first character returned.
• A positive position counts from the beginning of the source.
• A negative position counts backward from the end to locate the first returned character.
• substring_length is the number of characters returned and is optional.

Oracle positions begin at 1. Although Oracle treats a position of zero as 1, applications should specify 1 when the requirement means “begin with the first character.” Making that intent explicit avoids confusing position rules with zero-based indexes used by some programming languages.

Extracting a Fixed Number of Characters

The following query begins at the third character of PANDABEAR and returns four characters:

SELECT SUBSTR('PANDABEAR', 3, 4) AS substring_value
FROM   dual;

The positions are P at 1, A at 2, and N at 3. Beginning at N and returning four characters produces NDAB. The third argument describes the length of the returned substring; it is not the ending position.

A fixed segment can also be extracted from a structured identifier:

SELECT SUBSTR('INV-2026-0042', 5, 4) AS invoice_year
FROM   dual;

Position 5 is the first digit of the year, and the requested length is four, so the result is 2026. This technique is useful when a legacy or external format has stable positions. If the year is a business attribute used independently, however, storing it only inside an encoded string can make validation and querying unnecessarily difficult.

Omitting the Length

When substring_length is omitted, Oracle returns everything from the starting position through the end of the source. Both expressions in the following query return Database, but they reach the starting character in different ways:

SELECT SUBSTR('Oracle Database', 8)  AS from_position_eight,
       SUBSTR('Oracle Database', -8) AS final_eight_characters
FROM   dual;

The first expression counts forward to position 8. The second counts backward eight characters from the end. Once Oracle locates the starting character, the omitted length causes the remainder of the value to be returned.

If an explicitly supplied length is less than 1, SUBSTR returns null. It does not return a distinct empty character value. A null source also produces null, and a starting position beyond the available value produces no substring.

Extracting from the Left or Right

Some SQL dialects provide functions named LEFT and RIGHT. Oracle SQL expresses the equivalent requirements with SUBSTR. The positive and negative position forms communicate the direction directly.

SELECT SUBSTR('Hello World', 1, 5) AS first_five,
       SUBSTR('Hello World', -5)   AS last_five
FROM   dual;

The results are Hello and World. An expression such as SUBSTR(value, LENGTH(value) - n + 1, n) can also locate the final n characters, but SUBSTR(value, -n) states the requirement more directly.

When n comes from application input, validate that it is positive. A nonpositive substring length returns null, while zero used as a starting position follows the separate Oracle rule and is treated as position 1.

Character, Byte, and Unicode Variants

A character can require multiple bytes, and a user-perceived complete character can consist of multiple Unicode code points. Oracle therefore provides related functions whose positions and lengths use different units.

Use SUBSTRB only when the requirement is genuinely expressed in bytes and the encoding consequences are understood. A byte boundary can split a multibyte character, so byte-oriented extraction is normally inappropriate for names and other natural-language text.

Ordinary SUBSTR accepts CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The SUBSTRC, SUBSTR2, and SUBSTR4 variants do not accept CLOB or NCLOB. The return datatype generally follows the source, except a CHAR source returns VARCHAR2 and an NCHAR source returns NVARCHAR2.

Complete-Character Processing in Oracle AI Database 26ai

Starting with Oracle AI Database 26ai, SUBSTRC treats an Ideographic Variation Sequence as one complete character. Oracle also groups a character having the Unicode Mn general property with its preceding base character. A complete-character extraction therefore avoids separating the qualifying variation selector or combining mark from its base.

This enhancement does not make SUBSTRC the automatic choice for every string. Select it when the application's definition of position and length requires Unicode complete-character semantics. Choose the ordinary, byte, or code-point form only when those units match the requirement.

Combining SUBSTR with INSTR Safely

The Pet Store schema contains log text whose first sentence can vary in length. INSTR can locate the first period, and SUBSTR can use that position as the number of characters to return.

The period position itself is the correct length. If the period is at position 16, returning the first 16 characters includes it. Adding 1 would also request the following character, which is often a space and may be difficult to notice in displayed output.

A second condition must also be handled: INSTR returns zero when no period exists. Passing a calculated length of 1 would incorrectly reduce that log entry to its first character. The following query preserves the complete source when no period is found:

SELECT CASE
         WHEN INSTR(log_text, '.') > 0
         THEN SUBSTR(log_text, 1, INSTR(log_text, '.'))
         ELSE log_text
       END AS first_sentence
FROM   pet_care_log;

The query is a practical delimiter rule, not a complete natural-language sentence detector. A period can appear in an abbreviation, decimal value, hostname, or other nonterminal context. Applications that must interpret prose accurately need a more specialized sentence-boundary method.

Calculating the Delimiter Position Once

A common table expression can give the calculated position a name and avoid repeating the same INSTR call:

WITH positioned_logs AS (
  SELECT log_text,
         INSTR(log_text, '.') AS period_position
  FROM   pet_care_log
)
SELECT CASE
         WHEN period_position > 0
         THEN SUBSTR(log_text, 1, period_position)
         ELSE log_text
       END AS first_sentence
FROM   positioned_logs;

Both forms are valid Oracle SQL. The direct expression is concise, while the common table expression makes the intermediate position visible and easier to reuse in a larger query.

SQL and PL/SQL Usage

The preceding SELECT statements are SQL, not PL/SQL. PL/SQL can call the same functions in procedural expressions. A query inside a PL/SQL block must use an appropriate cursor construct or place selected scalar values into variables. The following complete block performs the same first-sentence transformation without a table query:

DECLARE
  source_text      VARCHAR2(100) := 'The dog is fine. Owner called later.';
  period_position  PLS_INTEGER;
  first_sentence   VARCHAR2(100);
BEGIN
  period_position := INSTR(source_text, '.');
  first_sentence := CASE
                      WHEN period_position > 0
                      THEN SUBSTR(source_text, 1, period_position)
                      ELSE source_text
                    END;
  DBMS_OUTPUT.PUT_LINE(first_sentence);
END;
/

The slash is a client command used by tools such as SQL*Plus and SQLcl to submit the completed PL/SQL block. It is not part of the PL/SQL language itself.

Modeling and Index Considerations

Repeatedly extracting a fixed segment can indicate that one column contains several business attributes. If the prefix, year, category, or other segment has independent meaning, storing it in a separate validated column can produce a clearer relational design than parsing it in every query.

Applying SUBSTR to a table column in a predicate can also affect use of an ordinary index. A frequently executed condition such as WHERE SUBSTR(account_code, 1, 3) = 'NE-' may require an appropriate function-based index. That decision should follow workload measurement and execution-plan analysis rather than being inferred from the function syntax alone.

Summary

• SUBSTR uses one-based positions; positive values count from the beginning and negative values count backward from the end.
• Omitting the length returns the remainder of the source, while a length below 1 returns null.
• SUBSTR(value, 1, n) returns the first n characters, and SUBSTR(value, -n) returns the last n.
• Select ordinary, byte, UCS-2, UCS-4, or Unicode complete-character semantics according to the actual data requirement.
• When an INSTR position defines the ending delimiter, use the delimiter position as the length and handle the zero result explicitly.
• Distinguish standalone SQL queries from PL/SQL blocks and use the appropriate execution structure for each.

The next lesson examines UPPER, INITCAP, RTRIM, and SOUNDEX for case conversion, trimming, and phonetic comparison.