Oracle SUBSTR Function Explained

In Oracle, the SUBSTR function is used to extract a substring from a string. It returns a part of a string, allowing you to specify both the starting position and the length to extract.

The syntax of the SUBSTR function is as follows: SUBSTR(string, start_position, [length])

Explanation of parameters:

1. String: the string to be extracted.
2. The starting position for the extraction. The position starts counting from 1, indicating the beginning of the string for the extraction.
3. Length: an optional parameter that specifies the desired length to be extracted. If this parameter is omitted, all characters from the starting position to the end of the string will be returned.

For example, assuming there is a string “Hello World”, the SUBSTR function can be used to achieve the following functionality:

1. The function SUBSTR(“Hello World”, 7) will return “World”, starting from the 7th character until the end.
2. Using SUBSTR(“Hello World”, 7, 3) will return “Wor”, which is a substring starting from the 7th character with a length of 3 characters.

It is important to note that both the starting position and length parameters of the SUBSTR function can be negative. Negative numbers indicate counting from the end of the string. For example, SUBSTR(“Hello World”, -3) returns “rld”, indicating to start extracting from the 3rd character from the end to the end.