SUBSTR() function extracts a substring from a string with various flexible options.
The following illustrates the syntax of the Oracle
SUBSTR( str, start_position [, substring_length, [, occurrence ]] );
SUBSTR() function accepts three arguments:
str is the string that you want to extract the substring. The data type of
str can be
start_position is an integer that determines where the substring starts. The following explains the effect of the
- If the
start_positionis 0, the begin of the substring will be at the first character of the
- In case the
start_positionis positive, the
SUBSTR()function will count from the beginning of the
strto determine the first character of the substring.
- If the
start_positionis negative, then the
SUBSTR()function will count backward from the end of the
strto find the first character of the substring.
substring_length determines the number of characters in the substring. If
substring_length is omitted, the
SUBSTR() function returns all characters starting from the
In case the
substring_length is less than 1, the
SUBSTR() function returns null.
SUBSTR() function returns a substring from the
str starting at
start_position with the
Let’s see the following example:
SELECT SUBSTR( 'Oracle Substring', 1, 6 ) SUBSTRING FROM dual;
In this example, the
SUBSTR() function returns a substring whose length is 6 starting from the beginning of the main string.
The following statement returns the same substring as above but uses a negative
SELECT SUBSTR( 'Oracle Substring', - 16, 6 ) SUBSTRING FROM dual;
Consider the following example:
SELECT SUBSTR( 'Oracle Substring', 8 ) SUBSTRING FROM dual;
In this example, we omitted the third argument (
substring_length) therefore the
SUBSTR() function returned all characters starting from the 8th character of the main string.
See the following
employees in the sample database:
The following statement uses the
COUNT() functions get employee names and their counts based on the initials.
SELECT SUBSTR( first_name, 1, 1 ) initials , COUNT( * ) FROM employees GROUP BY SUBSTR( first_name, 1, 1 ) ORDER BY initials;
In this tutorial, you have learned how to use the Oracle
SUBSTR() function to extract a substring from a string.