Oracle SUBSTR

The Oracle SUBSTR() function extracts a substring from a string with various flexible options.

Syntax

The following illustrates the syntax of the Oracle SUBSTR() function:

SUBSTR( str, start_position [, substring_length, [, occurrence ]] );Code language: SQL (Structured Query Language) (sql)

Arguments

The SUBSTR() function accepts three arguments:

str

str is the string that you want to extract the substring. The data type of str can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

start_position

start_position is an integer that determines where the substring starts. The following explains the effect of the start_position value:

• If the start_position is 0, the begin of the substring will be at the first character of the str.
• In case the start_position is positive, the SUBSTR() function will count from the beginning of the str to determine the first character of the substring.
• If the start_position is negative, then the SUBSTR() function will count backward from the end of the str to find the first character of the substring.

substring_length

substring_length determines the number of characters in the substring. If substring_length is omitted, the SUBSTR() function returns all characters starting from the start_position.

In case the substring_length is less than 1, the SUBSTR() function returns null.

Return value

The SUBSTR() function returns a substring from the str starting at start_position with the substring_length length.

Examples

Let’s see the following example:

SELECT
  SUBSTR( 'Oracle Substring', 1, 6 ) SUBSTRING
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

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 start_position value:

SELECT
  SUBSTR( 'Oracle Substring', - 16, 6 ) SUBSTRING
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

Consider the following example:

SELECT
  SUBSTR( 'Oracle Substring', 8 ) SUBSTRING
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

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 SUBSTR() and 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;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle SUBSTR() function to extract a substring from a string.