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.