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’

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. View all posts by sqlandplsql