Oracle PL/SQL Programming, Third Edition

Name

SUBSTR, SUBSTRB, SUBSTRC, SUBSTR2, and SUBSTR4

Synopsis

The SUBSTR family of functions is one of the most common and useful set of character functions. The SUBSTR functions allow you to extract a subset of contiguous characters from a string. The substring is specified by starting position and length, and the functions differ in the units they use:

SUBSTR

Starting position and length are in terms of characters.

SUBSTRB

Starting position and length are in terms of bytes. When you use a single-byte character set, SUBSTRB and SUBSTR will return the same results.

SUBSTRC

Starting position and length are in terms of Unicode characters, after any decomposed characters have been composed.

SUBSTR2

Starting position and length are in terms of code units.

SUBSTR4

Starting position and length are in terms of code points.

All of the function specifications follow the same pattern:

FUNCTION SUBSTR 
   (string_in IN VARCHAR2, 
    start_position_in IN NUMBER
    [, substr_length_in IN NUMBER])
RETURN VARCHAR2

where the arguments are as follows:

string_in

The source string

start_position_in

The starting position of the substring in string_in

substr_length_in

The length of the substring desired (the number of characters to be returned in the substring)

The last parameter, substr_length_in, is optional. If you do not specify a substring length, then SUBSTR returns all the characters to the end of string_in (from the starting position specified). The substr_length_in argument, if present, must be greater than zero. ...