SQL SUBSTR() Function

SQL SUBSTR() function return a selected string character from a full string.

SQL SUBSTR() function supported Oracle SQL version

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c
  • Oracle 18c

Syntax

SUBSTR(string, start_position, substring_length) 

Parameters

  • string is original string.
  • start_position is position to start substring.
  • substring_length is number of character we are extract from the original string.

If start_position is 0 then SUBSTR function count start as 1.
If start_position is positive number then SUBSTR function extract from beginning of the string.
If start_position is negative number then SUBSTR function extract from end of the string to count backside.


Example

Consider following example return extracted string from the original string.

SQL> SELECT SUBSTR('My self Opal Kole', 9, 12) "SUBSTR" FROM DUAL;

SUBSTR
---------
Opal Kole

SQL> SELECT SUBSTR('My self Opal Kole', -9, 17) "SUBSTR" FROM DUAL;

SUBSTR
---------
Opal Kole

SQL> SELECT SUBSTR('My self Opal Kole', 0, 12) "SUBSTR" FROM DUAL;

SUBSTR
------------
My self Opal

SQL> SELECT SUBSTR('My self Opal Kole', 0, 17) "SUBSTR" FROM DUAL;

SUBSTR
-----------------
My self Opal Kole