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:
1 | SELECT SUBSTR('Tech Honey',1,4) |
2 | FROM dual; |
Will return ‘Tech’
1 | SELECT SUBSTR('Tech Honey',1,6) |
2 | FROM dual; |
Will return ‘Tech H’
1 | SELECT SUBSTR('Tech Honey',3,6) |
2 | FROM dual; |
Will return ‘ch Hon’
1 | SELECT SUBSTR('Tech Honey',0,6) |
2 | FROM dual; |
Will return ‘Tech H’
1 | SELECT SUBSTR('Tech Honey',-4,6) |
2 | FROM dual; |
Will return ‘oney’
1 | SELECT SUBSTR('Tech Honey',-4,-6) |
2 | FROM dual; |
Will return NULL