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:
- input_string – It is used to specify the source string.
- start_position – It is used to specify the starting position for extraction.
- 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:
- Oracle 12c
- Oracle 11g
- Oracle 10g
- Oracle 9i
- Oracle 8i
Example-1: Passing all the three arguments in the SUBSTR function.
DECLARE Test_String string(25) := 'w3wiki'; 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) := 'w3wiki'; BEGIN dbms_output.put_line(SUBSTR(Test_String, '6')); END;
Output:
forBeginner
Example-3: Passing a negative value in the starting_position argument while passing the parameters to the SUBSTR function.
DECLARE Test_String string(25) := 'w3wiki'; 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) := 'w3wiki'; 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.
Contact Us