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.
What is the difficulty level of this exercise?
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics