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’