Oracle SUBSTR function

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

Syntax:

SUBSTR(string, start_position, length)

SUBSTR can be used in PL/SQL too.

Examples:-

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

SUB
—-
sql

Returns string of 3 character starting from position 1.

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

SUBS
—–
.com

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

begin
dbms_output.put_line(substr(‘sqlandplsql.com’,1,3));
end;

Some more examples

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

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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