INSTR(): Oracle PL/SQL INSTR() function examples
PL/SQL String Functions: INSTR()
Exercise 1:
Write a PL/SQL block to find the position of the first occurrence of the letter 'a' in 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_position NUMBER;
BEGIN
FOR emp IN (SELECT last_name FROM employees) LOOP
v_position := INSTR(emp.last_name, 'a');
DBMS_OUTPUT.PUT_LINE('Position of "a" in ' || emp.last_name || ': ' || v_position);
END LOOP;
END;
/
Sample Output:
Position of "a" in King : 0 Position of "a" in Kochhar : 6 Position of "a" in De Haan : 5 Position of "a" in Hunold : 0 Position of "a" in Ernst : 0 Position of "a" in Austin : 0 Position of "a" in Pataballa : 2 Position of "a" in Lorentz : 0 Position of "a" in Greenberg : 0 Position of "a" in Faviet : 2 .....
Explanation:
The said code in Oracle's PL/SQL retrieves the last names of employees from the 'employees' table to find the position of the character 'a' within each last name.
The v_position is declared as a NUMBER variable.
The FOR loop iterates over the result set obtained from the SELECT statement that retrieves only the last_name column from the employees table.
The loop variable emp is references each row in the result set.
The INSTR() function finds the position of the character 'a' within the current employee's last name and it stored in the v_position variable.
The DBMS_OUTPUT.PUT_LINE displays the position of 'a' in the employee's last name to the console, along with the text 'Position of "a" in' and the last name itself.
The loop continues until all rows in the result set have been processed.
Flowchart:
Exercise 2:
Create a PL/SQL procedure to check if a specific employee identified by their employee ID has the letter 'e' in their email. Return 1 if it exists, and 0 otherwise.
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 check_email_for_letter(
p_employee_id IN employees.employee_id%TYPE
) AS
v_emailemployees.email%TYPE;
v_result NUMBER;
BEGIN
SELECT email INTO v_email FROM employees WHERE employee_id = p_employee_id;
v_result := CASE WHEN INSTR(v_email, 'E') > 0 THEN 1 ELSE 0 END;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
END;
/
To execute the procedure:
DECLARE
employee_idemployees.employee_id%TYPE := 125; -- Provide the employee ID you want to pass
BEGIN
check_email_for_letter(employee_id);
END;
/
Sample Output:
Result: 1
Explanation:
The said code in Oracle's PL/SQL defines a procedure that takes an employee ID as an input parameter and retrieves the corresponding email from the employees table and checks if it contains the letter 'E'.
The procedure takes an input parameter p_employee_id of type employee_id in 'employees' table that specifies the employee whose email will be checked.
Two local variables v_email of type email and v_result of type NUMBER are declares.
The SELECT statement retrieves the email of the employee with the specified employee ID and stores it in the v_email variable.
The CASE statement checks whether the INSTR() function returns a value greater than 0 when searching for the letter 'E' in the email. If so, it assigns the value 1 to v_result; otherwise, it assigns 0.
The DBMS_OUTPUT.PUT_LINE procedure displays the result of the check to the console, along with the text 'Result:'.
Flowchart:
Exercise 3:
Write a PL/SQL function to return the number of occurrences of the letter 'S' in the job title of 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 FUNCTION count_letter_in_job_title(
p_employee_id IN employees.employee_id%TYPE
) RETURN NUMBER AS
v_job_titleemployees.job_id%TYPE;
BEGIN
SELECT job_id INTO v_job_title FROM employees WHERE employee_id = p_employee_id;
RETURN REGEXP_COUNT(v_job_title, 'S');
END;
/
To execute the function:
DECLARE
employee_idemployees.employee_id%TYPE := 125; -- Provide the employee ID you want to pass
result NUMBER;
BEGIN
result := count_letter_in_job_title(employee_id);
DBMS_OUTPUT.PUT_LINE('Letter Count: ' || result);
END;
/
Sample Output:
Letter Count: 1
Explanation:
The said code in Oracle's PL/SQL defines a function that takes an employee ID as an input parameter and retrieves the corresponding job title from the employees table and counts the occurrences of the letter 'S' in the job title.
The function takes an input parameter p_employee_id of type employee_id to specify the employee whose job title will be counted.
A local variable named v_job_title of type job_id is declared.
The SELECT statement retrieves the job ID of the employee with the provided employee ID and stores it in the v_job_title variable.
The RETURN statement returns the number of the occurrences of the letter 'S' in the job title stored in v_job_title by the REGEXP_COUNT() function.
Flowchart:
Exercise 4:
Write a PL/SQL block that retrieves the first name and the position of the substring 'IT' in the job title for all employees in the employees table. Display the results.
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_nameemployees.first_name%TYPE;
v_position NUMBER;
BEGIN
FOR emp IN (SELECT first_name, job_id FROM employees) LOOP
v_position := INSTR(emp.job_id, 'IT');
v_first_name := emp.first_name;
DBMS_OUTPUT.PUT_LINE('First Name: ' || v_first_name || ', Position: ' || v_position);
END LOOP;
END;
/
Sample Output:
First Name: Steven , Position: 0 First Name: Neena , Position: 0 First Name: Lex , Position: 0 First Name: Alexander , Position: 1 First Name: Bruce , Position: 1 First Name: David , Position: 1 First Name: Valli , Position: 1 First Name: Diana , Position: 1 First Name: Nancy , Position: 0 First Name: Daniel , Position: 0 First Name: John , Position: 0 First Name: Ismael , Position: 0 First Name: Jose Manue , Position: 0 First Name: Luis , Position: 0 ....
Explanation:
The said code in Oracle's PL/SQL retrieves the first names and job IDs of employees from the "employees" table to find the position of the substring 'IT' within each job ID.
The variable v_first_name is declared the same type as the employees.first_name column and v_position as a NUMBER variable.
The FOR loop iterates over the result set obtained from the SELECT statement that fetches both first_name and job_id columns from the employees table.
The loop variable emp references each row in the result set and inside the loop, the INSTR() function finds the position of the substring 'IT' within the job ID of the current employee and stored in the v_position variable.
The first name of the employee is assigned to the v_first_name variable.
The DBMS_OUTPUT.PUT_LINE procedure is displays the first name and the position of 'IT' in the job ID of the employee to the console.
The loop continues until all rows in the result set have been processed.
Flowchart:
Exercise 5:
Write a PL/SQL block that prompts the user to enter a substring. Use the LOCATE function to find the position of the substring in the job titles of employees in the employees table. Display the employee ID, first name, and last name for the employees whose job title contains the entered substring.
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_substringVARCHAR2(50);
v_position NUMBER;
BEGIN
-- Prompt the user to enter a substring
v_substring := '&Enter a substring: ';
FOR emp IN (SELECT employee_id, first_name, last_name, job_id FROM employees) LOOP
v_position := INSTR(emp.job_id, v_substring);
IF v_position> 0 THEN
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.employee_id || ', First Name: ' || emp.first_name || ', Last Name: ' || emp.last_name);
END IF;
END LOOP;
END;
/
To execute:
DECLARE
v_substringVARCHAR2(50);
v_position NUMBER;
BEGIN
-- Prompt the user to enter a substring
v_substring := 'CLE';
FOR emp IN (SELECT employee_id, first_name, last_name, job_id FROM employees) LOOP
v_position := INSTR(emp.job_id, v_substring);
IF v_position> 0 THEN
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.employee_id || ', First Name: ' || emp.first_name || ', Last Name: ' || emp.last_name);
END IF;
END LOOP;
END;
/
Sample Output:
Employee ID: 115, First Name: Alexander , Last Name: Khoo Employee ID: 116, First Name: Shelli , Last Name: Baida Employee ID: 117, First Name: Sigal , Last Name: Tobias Employee ID: 118, First Name: Guy , Last Name: Himuro Employee ID: 119, First Name: Karen , Last Name: Colmenares Employee ID: 125, First Name: Julia , Last Name: Nayer Employee ID: 126, First Name: Irene , Last Name: Mikkilinen Employee ID: 127, First Name: James , Last Name: Landry Employee ID: 128, First Name: Steven , Last Name: Markle Employee ID: 129, First Name: Laura , Last Name: Bissot
Explanation:
The said code in Oracle's PL/SQL prompts the user to enter a substring, and after searching that substring within the job IDs of employees in the 'employees' table returns employee ID, first name, and last name to the console if a match is found.
The variable v_substring is declared as a VARCHAR2 type with a maximum length of 50 characters and v_position is declared as a NUMBER variable.
The user is prompted to enter a substring that value is stored in the v_substring variable.
The FOR loop iterates over the result set obtained from the SELECT statement, which retrieves the employee ID, first name, last name, and job ID of employees from the employees table.
The loop variable emp references each row in the result set.
Inside the loop, the INSTR() function finds the position of the specified substring within the job ID of the current employee and it stored in the v_position variable.
If the position is greater than 0, indicating that the substring is found in the job ID, the employee ID, first name, and last name are displays to the console using DBMS_OUTPUT.PUT_LINE.
The loop continues until all rows in the result set have been processed.
Flowchart:
Exercise 6:
Write a PL/SQL block that finds the position of the first occurrence of the substring 'MAN' in the department name for all departments in the departments table. Display the department ID, department name, and the position of the substring.
Table: departments
department_id integer department_name varchar(25) manager_id integer location_id integer
PL/SQL Code:
DECLARE
v_dept_iddepartments.department_id%TYPE;
v_dept_namedepartments.department_name%TYPE;
v_position NUMBER;
BEGIN
FOR dept IN (SELECT department_id, department_name FROM departments) LOOP
v_position := INSTR(upper(dept.department_name), 'MAN');
v_dept_id := dept.department_id;
v_dept_name := dept.department_name;
DBMS_OUTPUT.PUT_LINE('Department ID: ' || v_dept_id || ', Department Name: ' || v_dept_name || ', Position: ' || v_position);
END LOOP;
END;
Sample Output:
Department ID: 10, Department Name: Administration, Position: 0 Department ID: 20, Department Name: Marketing, Position: 0 Department ID: 30, Department Name: Purchasing, Position: 0 Department ID: 40, Department Name: Human Resources, Position: 3 Department ID: 50, Department Name: Shipping, Position: 0 Department ID: 60, Department Name: IT, Position: 0 Department ID: 70, Department Name: Public Relation, Position: 0 Department ID: 80, Department Name: Sales, Position: 0 Department ID: 90, Department Name: Executive, Position: 0 Department ID: 100, Department Name: Finance, Position: 0 Department ID: 110, Department Name: Accounting, Position: 0 Department ID: 120, Department Name: Treasury, Position: 0 Department ID: 130, Department Name: Corporate Tax, Position: 0 Department ID: 140, Department Name: Control And Cre, Position: 0 Department ID: 150, Department Name: Shareholder Ser, Position: 0 Department ID: 160, Department Name: Benefits, Position: 0 Department ID: 170, Department Name: Manufacturing, Position: 1 .....
Explanation:
The said code in Oracle's PL/SQL that calculates the position of the substring 'MAN' in the department name, and displays the department ID, department name, and the position of the substring for each row.
The variable v_dept_id , and the v_dept_name are of the same data type as the department_id and the department_name column in the 'departments' table and v_position a numeric variable to store the position of the substring 'MAN' within the department name.
The FOR loop selects all the rows from the 'departments' table and assigns each row to the dept variable.
The INSTR function finds the position of the substring 'MAN' within the department name and returns 0 if not found.
The v_dept_id is assigned by the value of department_id and the v_dept_name is assigned by the value of department_name from the current row in the loop.
The DBMS_OUTPUT.PUT_LINE displays the department ID, department name, and the position of the substring 'MAN' within the department name.
Flowchart:
Improve this sample solution and post your code through Disqus
Previous: CONCAT() Functions.
Next: SUBSTR() Function.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics