Oracle / PLSQL: SUBSTR Function

This article is written about how to use the Oracle/PLSQL SUBSTR feature with syntax and examples.


The Oracle/PLSQL SUBSTR features lets in you to extract a substring from a string.


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 beginning role for extraction. The first function in the string is always 1. length Optional. It is the variety of characters to extract. If this parameter is omitted, the SUBSTR function will return the complete string.


The SUBSTR characteristic returns a string value. If length is a bad number, then the SUBSTR feature will return a NULL value.


If start_position is 0, then the SUBSTR characteristic treats start_position as 1 (ie: the first function in the string). If start_position is a advantageous number, then the SUBSTR characteristic begins from the opening of the string. If start_position is a negative number, then the SUBSTR characteristic starts offevolved from the stop of the string and counts backwards. See also the REGEXP_SUBSTR function.

Applies To

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

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


Let’s seem at some Oracle SUBSTR feature examples and discover how to use the SUBSTR characteristic 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'