The SUBSTR Function in Oracle SQL / PLSQL is used to extract a string from within another string.
Syntax for the using the SUBSTR Function in Oracle SQL / PLSQL is:
SELECT SUBSTR(string_1, start_position [,length])
FROM table_name;
- string_1 is the string from which the substring is to be extracted.
- start_position is the position in string_1 from where the extraction will start, the first position is always 1.
- length is optional parameter, it describes the number of characters to be extracted, if omitted, the SUBSTR function returns the string_1 from the start_position provided.
Also,
- If start_position is 0, then start_position is treated as 1.
- If start_position is any positive number then SUBSTR function starts counting from the beginning of the string.
- If start_position is any negative number then SUBSTR function starts counting from the end of the string, counting backwards.
- If length is passed as a negative number then SUBSTR function returns NULL.
Examples:
Using SUBSTR Function in Oracle SQL / PLSQL SELECT statement:
SELECT SUBSTR('Tech Honey',1,4)
FROM dual;
Will return ‘Tech’
SELECT SUBSTR('Tech Honey',1,6)
FROM dual;
Will return ‘Tech H’
SELECT SUBSTR('Tech Honey',3,6)
FROM dual;
Will return ‘ch Hon’
SELECT SUBSTR('Tech Honey',0,6)
FROM dual;
Will return ‘Tech H’
SELECT SUBSTR('Tech Honey',-4,6)
FROM dual;
Will return ‘oney’
SELECT SUBSTR('Tech Honey',-4,-6)
FROM dual;
Will return NULL