SUBSTR(): Oracle PL/SQL SUBSTR Function Examples

PL/SQL String Functions: SUBSTR()

Exercise 1:

Write a PL/SQL block to retrieve the first 3 characters of each employee's last name in the employees table.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
v_first_three_charsVARCHAR2(3);
BEGIN
  FOR emp IN (SELECT last_name FROM employees) LOOP
v_first_three_chars := SUBSTR(emp.last_name, 1, 3);
    DBMS_OUTPUT.PUT_LINE('First three characters of last name: ' || v_first_three_chars);
  END LOOP;
END;
/

Sample Output:

First three characters of last name: Kin
First three characters of last name: Koc
First three characters of last name: De 
First three characters of last name: Hun
First three characters of last name: Ern
First three characters of last name: Aus
First three characters of last name: Pat
First three characters of last name: Lor
First three characters of last name: Gre
First three characters of last name: Fav
.......

Explanation:

The said code in Oracle's PL/SQL that extract the first three characters of last name of each employees from the 'employees' table.

The variable v_first_three_chars is declared as a VARCHAR2 type that can store up to three characters.

The FOR loop iterates over the result set obtained from the SELECT statement, which retrieves the last_name column from the employees table and the SUBSTR function extracts the first three characters of the last name from the current row, storing the result in v_first_three_chars.

The DBMS_OUTPUT.PUT_LINE displays the extracted characters along with a descriptive message.

Flowchart:

Exercise 2:

Create a PL/SQL procedure to retrieve the leftmost n characters of the email for a specific employee identified by their employee ID.

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

CREATE OR REPLACE PROCEDURE get_left_email(
p_employee_id IN employees.employee_id%TYPE,
p_num_chars IN NUMBER
) AS
v_emailemployees.email%TYPE;
v_left_emailVARCHAR2(100);
BEGIN
  SELECT email INTO v_email FROM employees WHERE employee_id = p_employee_id;
v_left_email := SUBSTR(v_email, 1, p_num_chars);
  DBMS_OUTPUT.PUT_LINE('Left ' || p_num_chars || ' characters of email: ' || v_left_email);
END;
/
To execute the procedure:
DECLARE
p_employee_idemployees.employee_id%TYPE := 125; 
p_num_chars NUMBER := 5; BEGIN
get_left_email(p_employee_id, p_num_chars);
END;
/

Sample Output:

Left 5 characters of email: JNAYE

Explanation:

The said code in Oracle's PL/SQL defines a procedure that retrieves a specific employee's email from the 'employees' table and extracts a specified number of leftmost characters from it.

The procedure accepts two parameters p_employee_id of the data type same as the employee_id column in the 'employees' table and p_num_chars of the NUMBER data type, representing the desired number of characters to extract.

Two variables v_email of the same data type as the email column in the 'employees' table and v_left_email as a VARCHAR2 with a maximum length of 100 characters are declares.

The SUBSTR function is then extracts the leftmost p_num_chars characters from the v_email variable and assigned the result to v_left_email.

The DBMS_OUTPUT.PUT_LINE function displays a message containing the number of characters requested and the extracted substring.

Flowchart:

Exercise 3:

Write a PL/SQL function to retrieve the leftmost 5 characters of the department name for a specific department identified by its department ID.

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

CREATE OR REPLACE FUNCTION get_left_department_name(
p_department_id IN departments.department_id%TYPE
) RETURN VARCHAR2 AS
v_department_namedepartments.department_name%TYPE;
v_resultVARCHAR2(5);
BEGIN
  BEGIN
    SELECT department_name INTO v_department_name FROM departments WHERE department_id = p_department_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
v_department_name := NULL;  END;
  IF v_department_name IS NULL THEN
v_result := NULL;
  ELSE
v_result := SUBSTR(v_department_name, 1, 5);
  END IF;

  RETURN v_result;
END;
/
To execute the function:
DECLARE
v_resultVARCHAR2(5);
BEGIN
v_result := get_left_department_name(p_department_id =>90);  DBMS_OUTPUT.PUT_LINE('Left 5 characters of department name: ' || v_result);
END;
/

Sample Output:

Left 5 characters of department name: Execu

Explanation:

The said code in Oracle's PL/SQL defines a function that retrieves the first five characters of the department name based on the provided department ID. If a department with the given ID is found, it returns the first five characters otherwise, it returns NULL.

The two variables v_department_name, which is the data type of the department_name column in the departments table, and v_result, which is a VARCHAR2 type of length 5 is declares.

After the exception handling block, there is an IF statement that checks whether v_department_name is NULL. If it is NULL, the v_result variable is also set to NULL. Otherwise, the SUBSTR function extracts the first five characters of v_department_name, and that value is assigned to v_result.

Finally, the function returns the value of v_result.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: INSTR() Functions.
Next: LOWER() Function.