Different uses of SUBSTR function in Oracle are shown here.

Example-1: use of SUBSTR without length

SELECT
SUBSTR( 'Advanced Database Management System', 9 ) TEXT
FROM
dual;

Output:

Example-2: use of SUBSTR with positive length

SELECT
SUBSTR( 'Advanced Database Management System', 9,8 ) TEXT
FROM
dual;

Output:

Example-3: use of SUBSTR with negative length

SELECT
SUBSTR( 'Advanced Database Management System', -6,6 ) TEXT
FROM
dual;

Example-4: use of SUBSTR with table

SELECT
SUBSTR( acc_create_date, -2 ) year ,
COUNT( * )
FROM
members
GROUP BY
SUBSTR( acc_create_date, -2 )
ORDER BY
year;

You will get the explanation in the following video.

Exercise: Create a table named employees (id, name, joining_date, post, department) with two or more records. Find out the number of employees who joined 1^st January of any year using SUBSTR function.