w3resource

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:

Flowchart: PL/SQL String Function Exercises - SUBSTR() function

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:

Flowchart: PL/SQL String Function Exercises - SUBSTR() function

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:

Flowchart: PL/SQL String Function Exercises - SUBSTR() function

Improve this sample solution and post your code through Disqus

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

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/plsql-exercises/string-functions/plsql-substr-exercise-7.php