PLSQL | SUBSTR Function

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.



My Personal Notes arrow_drop_up

I am a technology enthusiast who has a keen interest in programming I am pursuing Engineering in Computer Science from GEU, Dehradun I like to unwind by watching movies and English sitcomsI have a keen interest in music

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.