SUBSTR function in Oracle pl/sql

SUBSTR function in Oracle Pl Sql

In real time projects the requirement might come like to extract the length of data from the particular data. In this blog, we will see how we can execute this requirement through Oracle pl sql by using Oracle/PLSQL SUBSTR function.  

Syntax of SUBSTR function in Oracle PL SQL is:

SUBSTR( string, starting_position [, Total length of the data ] )

Parameters of SUBSTR

string

Input data which we are going to use for extraction process.

starting position

It denotes the starting position of length of the data. The position of the string will always start from 1.

length

Optional. It denotes the length of the character to extract.

If this parameter is not given, then the SUBSTR function will return the entire string from the starting position which we given in syntax.

The SUBSTR function is used to returns a string value.

If length of the data is a negative number, then the SUBSTR function will return a NULL value.

Rule of SUBSTR function in Oracle pl sql:

• 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 +ve number, then the SUBSTR function starts from the beginning of the string.
• If start_position is a -ve number, then the SUBSTR function starts from the end of the string and counts backwards.

SUBSTR function support in below version of Oracle pl sql.

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

How to use SUBSTR function in Oracle/PLSQL

SUBSTR('SUBSTRfunction in Oracle pl sql', 6, 2)
Output: 'Rf'

SUBSTR('SUBSTRfunction in Oracle pl sql', 6)
Output: 'Rfunction in Oracle pl sql'

SUBSTR('SUBSTRfunction in Oracle pl sql', 1, 4)
Output: 'SUBS'

SUBSTR('SUBSTRfunction in Oracle pl sql', -3, 3)
Output: 's l'

SUBSTR('SUBSTRfunction in Oracle pl sql', -6, 3)
Output: 's pl e'

                         
                  Follow us for more blog and Best Oracle Training in Chennai