Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function

SQL SUBSTR Function


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

Supported Oracle SQL Version :

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

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