Oracle SUBSTR() Function

Oracle SUBSTR() is a built-in function that returns a specified length substring starting from a specified position in a string.

Oracle has several similar functions, but they calculate the length in different ways:

  • SUBSTR calculates the length based on the characters defined in the input character set.
  • SUBSTRB calculates the length based on bytes instead of characters.
  • SUBSTRC calculates the length based on full Unicode characters.
  • SUBSTR2 calculates the length based on UCS2 code points.
  • SUBSTR4 calculates the length based on UCS4 code points.

If you need to match a substring from the source string, use REGEXP_SUBSTR().

Oracle SUBSTR() Syntax

Here is the syntax for the Oracle SUBSTR() function:

SUBSTR(char, position [, substring_length ])

Parameters

char

Required. The original string from which the substring is to be extracted. It can be any data type in CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

position

Required. The starting position for the extraction. It can be a positive or negative number. If it is positive, the position to start extracting the substring is determined from the beginning of the string. If it is negative, the position to start extracting the substring is determined from the end of the string.

substring_length

Optional. The length of the substring in characters. If not specified, the substring will be extracted until the end of the original string.

Return Value

The Oracle SUBSTR() function returns a specified length substring starting from a specified position in a string.

  • If position is 0, it is treated as 1.
  • If position is positive, Oracle counts from the beginning of char to find the first character.
  • If position is negative, Oracle counts from the end of char backward.
  • If substring_length is omitted, Oracle returns all characters from the end of char. If substring_length is less than 1, Oracle returns NULL.

Oracle SUBSTR() Examples

Here are some examples demonstrating the usage of the Oracle SUBSTR() function.

Basic Usage

SELECT
    SUBSTR('Hello World', 7) Result
FROM dual;

Output:

RESULT
_________
World

Substring Length

The Oracle SUBSTR() function allows you to specify the length of the string.

SELECT
    SUBSTR('Hello World', 8, 2) Result
FROM dual;

Output:

RESULT
_________
or

Negative Position

The Oracle SUBSTR() function allows you to specify a negative position parameter. Specifying a negative value for the position causes the starting position to be counted backward from the end of the string:

SELECT
    SUBSTR('Hello World', -5) Result
FROM dual;

Output:

RESULT
_________
World

NULL Parameters

If any parameter is NULL, SUBSTR() returns NULL.

SET NULL 'NULL';
SELECT
    SUBSTR(NULL, 3) Result1,
    SUBSTR('A', NULL) Result2,
    SUBSTR('A', 1, NULL) Result3,
    SUBSTR(NULL, NULL, NULL) Result4
FROM dual;

Output:

RESULT1    RESULT2    RESULT3    RESULT4
__________ __________ __________ __________
NULL       NULL       NULL       NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle SUBSTR() is a built-in function that returns a specified length substring starting from a specified position in a string.