Oracle SUBSTR function

SUBSTR function returns the sub string within a string. It is an example of character functions.


SUBSTR(string, start_position, length)

SUBSTR can be used in PL/SQL too.


1. select substr(‘’,1,3) from dual;


Returns string of 3 character starting from position 1.

2. select substr(‘’,-4,4) from dual;


Returns string from end ( 4 characters from end is .com)

3. select dob,substr(dob,-4,4) from emp where empno = 10;

DOB                    SUBS
—————-        ——
02-JAN-1950      1950

4. SUBSTR can be used in PL/SQL


Some more examples

select substr(‘’,1,null) from dual; —> null
select substr(‘’,null,null) from dual; —> null
select substr(‘’,0,0) from dual; —> null
select substr(‘’,1,-2) from dual; —> null
select substr(‘’,0,3) from dual; —> ‘sql’
select substr(‘’,0) from dual; —> ‘’
select substr(‘’,1) from dual; —> ‘’


Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s