The oracle SUBSTR functions return a portion of string, beginning at a specified position in the string.This functions allows to extract a substring from a string.To know the position of a string in a string you can use INSTR function.
Syntax:
substr( string, start_position, [ length ] )
where,
string :Is the source string.
start_position :Is the start position for substring. If it is a negative number substr starts from the end of the string and counts backwards.
length :Is the number of characters to extract,The parameter is optional and if omitted, substr will return the entire string.
The substr accepts CLOB also as parameter then the function will return CLOB as result.
Examples:
1.start_position 1.
SELECT SUBSTR('PL/SQLworld technology tips', 1, 6) RESULT FROM dual;
RESULT
PL/SQL
2.Middle string
SQL> SELECT SUBSTR('PL/SQLworld technology tips', 7, 5) RESULT FROM dual;
RESULT
world
3. length : optional
SQL> SELECT SUBSTR('PL/SQLworld technology tips', 12) RESULT FROM dual;
RESULT
technology tips
4. start_position :Negative
SQL> SELECT SUBSTR('PL/SQLworld technology tips', -4) RESULT FROM dual;
RESULT
tips
Related functions:
SUBSTR calculates lengths using characters as defined by the input character set.
SUBSTRB calculates lengths using bytes.
SUBSTRB calculates lengths using Unicode complete characters.
SUBSTR2 calculates lengths using UCS2 code points.
SUBSTR4 calculates lengths using UCS4 code points.