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.

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.

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

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'

Share on Google Plus

About Mindsforest