Oracle Plsql Substr Function
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
- start_position
- length
The source string.
The starting position for extraction. The first position in the string is always 1.
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:
select SUBSTR('This is a test', 6, 2)
Output: 'is'
SUBSTR('This is a test', 6)
Output: 'is a test'
SUBSTR('aodba', 1, 4)
Output: 'aodb'