totn Oracle Functions

Oracle / PLSQL: SUBSTR Function

This Oracle tutorial explains how to use the Oracle/PLSQL SUBSTR function with syntax and examples.

Description

The Oracle/PLSQL SUBSTR functions allows you to extract a substring from a string.

Syntax

The syntax for the SUBSTR function in Oracle/PLSQL is:

SUBSTR( string, start_position [, length ] )

Parameters or Arguments

string
The source string.
start_position
The starting position for extraction. The first position in the string is always 1.
length
Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.

Returns

The SUBSTR function returns a string value.
If length is a negative number, then the SUBSTR function will return a NULL value.

Note

  • If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).
  • If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.
  • If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.
  • See also the REGEXP_SUBSTR function.

Applies To

The SUBSTR function can be used in the following versions of Oracle/PLSQL:

  • Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example

Let's look at some Oracle SUBSTR function examples and explore how to use the SUBSTR function in Oracle/PLSQL.

For example:

SUBSTR('This is a test', 6, 2)
Result: 'is'

SUBSTR('This is a test', 6)
Result: 'is a test'

SUBSTR('TechOnTheNet', 1, 4)
Result: 'Tech'

SUBSTR('TechOnTheNet', -3, 3)
Result: 'Net'

SUBSTR('TechOnTheNet', -6, 3)
Result: 'The'

SUBSTR('TechOnTheNet', -8, 2)
Result: 'On'