Returning Character Value

Oracle SUBSTR Function Examples

Oracle SUBSTR Function by Examples

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.

Please note that, unaware of using MySQL functions in Oracle SQL statements may cause ORA-00904 invalid identifier. SUBSTR is an Oracle function and also a MySQL function, same name, same usage.

Leave a Reply

Your email address will not be published. Required fields are marked *