Oracle SUBSTR Function: usage Explained

The SUBSTR function in Oracle is used to extract a substring from a string. The basic syntax is as follows:

Extracts a part of a string from a specified starting position with an optional length.

1. String: The string from which to extract a substring.
2. start_position: The starting position of the substring, counting from 1.
3. length: the length of the substring to extract.

If the length parameter is omitted, the SUBSTR function will extract from the start_position to the end of the string; if the length parameter is provided, the SUBSTR function will extract a substring of the specified length starting from the start_position.

Here are some examples:

1. Extract the substring starting from the third position:

SELECT SUBSTR('Oracle', 3) FROM dual;
-- 结果为 'acle'

1. Extract a substring of length 3 starting from the 5th position:

SELECT SUBSTR('Oracle', 5, 3) FROM dual;
-- 结果为 'le'

1. Extract the substring starting from the second position to the third position from the end:

SELECT SUBSTR('Oracle', 2, -3) FROM dual;
-- 结果为 'ra'

It is important to note that in Oracle, string indexes start counting from 1 and negative indexes can be used to count from the end of the string.