SUBSTR Function
Oracle SUBSTR function is used to cut or extract a piece of sub-string from an outer string. In this post, I will introduce how we use Oracle SUBSTR function with some simple examples.
1. Cut the first word from the input string 'Oracle SQL Functions'.
Oracle SUBSTR searches for position 1 and following 6 characters.
SQL> select substr('Oracle SQL Functions', 1, 6) "SUBSTR" from dual;
SUBSTR
------
Oracle
Even though we use 0 as our starting position, Oracle treats it as 1.
SQL> select substr('Oracle SQL Functions', 0, 6) "SUBSTR" from dual;
SUBSTR
------
Oracle
That is to say, either 0 or 1 is acceptable for the function arguments.
2. Cut the second word from the input string 'Oracle SQL Functions'.
Oracle SUBSTR searches for position 8 and following 3 characters.
SQL> select substr('Oracle SQL Functions', 8, 3) "SUBSTR" from dual;
SUB
---
SQL
Even though we use a floating-point number as position, Oracle treats it as an integer.
SQL> select substr('Oracle SQL Functions', 8.7, 3) "SUBSTR" from dual;
SUB
---
SQL
Also, we can use a floating-point number as length.
SQL> select substr('Oracle SQL Functions', 8, 3.7) "SUBSTR" from dual;
SUB
---
SQL
In normal situations, using floating-point number is meaningless.
3. Cut the third word from the input string 'Oracle SQL Functions'.
Oracle SUBSTR searches for position 12 and following 9 characters.
SQL> select substr('Oracle SQL Functions', 12, 9) "SUBSTR" from dual;
SUBSTR
---------
Functions
If we omit the length, Oracle SUBSTR takes the rest from the starting position to the right-most one.
SQL> select substr('Oracle SQL Functions', 12) "SUBSTR" from dual;
SUBSTR
---------
Functions
We can take advantage of the backward positioning to cut the last word if the input string is too long to count the position.
For example, we have already known 'Functions' has 9 characters, then we can do this:
SQL> select substr('Oracle SQL Functions', -9, 9) "SUBSTR" from dual;
SUBSTR
---------
Functions
By the way, any length less than 1 returns null.
SQL> select substr('Oracle SQL Functions', 8, -1) "SUBSTR" from dual;
S
-
Null is returned.
Oracle SUBSTR Considerations
Please note that, unaware of using MySQL functions in Oracle SQL statements may cause ORA-00904 invalid identifier. The interesting thing is that MySQL also has the same name function MySQL SUBSTR as Oracle's.