Skip to content
Related Articles

Related Articles

PLSQL | SUBSTR Function
  • Last Updated : 30 Sep, 2019

The PLSQL SUBSTR function is used for extracting a substring from a string.
The SUBSTR function accepts three parameters which are input_string, start_position, length.
SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters.

Note:

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

Syntax:

SUBSTR( input_string, start_position, length)

Parameters Used:

  1. input_string – It is used to specify the source string.
  2. start_position – It is used to specify the starting position for extraction.
  3. length – It is an optional parameter which is used to specify the number of characters to extract.

Return Value:
The SUBSTR function in PLSQL returns a string value.



Supported Versions of Oracle/PLSQL:

  1. Oracle 12c
  2. Oracle 11g
  3. Oracle 10g
  4. Oracle 9i
  5. Oracle 8i

Example-1: Passing all the three arguments in the SUBSTR function.

DECLARE 
   Test_String string(25) := 'Geeksforgeeks';
   
BEGIN 
   dbms_output.put_line(SUBSTR(Test_String, '6', '3')); 
   
END;      

Output:

for

Example-2: Omitting the length argument while passing parameters to the SUBSTR function.

DECLARE 
   Test_String string(25) := 'Geeksforgeeks';
   
BEGIN 
   dbms_output.put_line(SUBSTR(Test_String, '6')); 
   
END;    

Output:

forgeeks 

Example-3: Passing a negative value in the starting_position argument while passing the parameters to the SUBSTR function.

DECLARE 
   Test_String string(25) := 'Geeksforgeeks';
   
BEGIN 
   dbms_output.put_line(SUBSTR(Test_String, '-6', '3')); 
   
END;     

Output:

rge 

SUBSTR function starts from the end of the string and counts backwards if the starting-position argument has a negative value.

Example-4: Passing a value in the starting_position argument which is greater than the number of characters in the input_string.

DECLARE 
   Test_String string(25) := 'Geeksforgeeks';
   
BEGIN 
   dbms_output.put_line(SUBSTR(Test_String, '-16', '3')); 
   
END; 

Output:

NULL 

SUBSTR function returns NULL since the number of characters present in the input_string is less than the value passed in the starting-position argument.

Advantage:
Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :