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; —> ‘’

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s