SUBSTR function
SUBSTR retrieves a portion of the string.
The general format for this function is:
SUBSTR(string, start_at_position[, number_of_characters_to_retrieve])
SQL> SELECT SUBSTR('ThisIsATest', 5, 3) FROM dual;
SUB
---
IsA
SQL>
The effect of starting point:
SQL> SELECT SUBSTR('My address is 123 Main St.',1,12) FROM dual;
SUBSTR('MYAD
------------
My address i
SQL> SELECT SUBSTR('My address is 123 Main St.',5,12) From dual;
SUBSTR('MYAD
------------
ddress is 12
SQL>
If no number_of_characters_to_retrieve is specified, then the rest of the string from the begin point is retrieved.
SQL> SELECT SUBSTR('ThisIsATest', 5) FROM dual;
SUBSTR(
-------
IsATest
SQL>
SQL>
If start_at_position is negative then the retrieval starts from the right-hand side the the string.
SQL> SELECT SUBSTR('My address is 123 Main St.',-1,12) FROM dual;
S
-
.
SQL> SELECT SUBSTR('My address is 123 Main St.',-5,12) From dual;
SUBST
-----
n St.
SQL>
Using SUBSTR and INSTR together.
SQL> SELECT SUBSTR('FirstName, Last', INSTR('FirstName, Last',', ')+2) FROM dual;
SUBS
----
Last
SQL>