ASCII(): ASCII operations in Oracle PL/SQL

PL/SQL String Functions: ASCII()

Exercise 1:

Write a PL/SQL block to display the ASCII value of each character in the employee's last name for all records 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_ascii NUMBER;
BEGIN
  FOR emp IN (SELECT last_name FROM employees) LOOP
    FOR i IN 1..LENGTH(emp.last_name) LOOP
v_ascii := ASCII(SUBSTR(emp.last_name, i, 1));
      DBMS_OUTPUT.PUT_LINE('ASCII value of character ' || SUBSTR(emp.last_name, i, 1) || ': ' || v_ascii);
    END LOOP;
  END LOOP;
END;
/

Sample Output:

ASCII value of character K: 75
ASCII value of character i: 105
ASCII value of character n: 110
ASCII value of character g: 103
ASCII value of character : 32
ASCII value of character : 32
ASCII value of character : 32
ASCII value of character : 32

Explanation:

The said code in Oracle's PL/SQL that retrieves the ASCII values for the characters in the last names of employees stored in the "employees" table.

Inside the block, a variable named "v_ascii" of type NUMBER is declared.

The "FOR" loop is iterates over each row in the result set obtained from the SELECT query.

The another nested "FOR" loop for each employee's last name iterates through each character in the last name.

Within the inner loop, the "SUBSTR" function extracts each individual character from the last name.

The "ASCII" function is then obtain the corresponding ASCII value for the extracted character and assigned to the "v_ascii" variable.

The "DBMS_OUTPUT.PUT_LINE" statement displays the ASCII value along with the character itself using concatenation.

Flowchart:

Exercise 2:

Create a PL/SQL procedure to calculate the sum of ASCII values for all characters in the first name of a specific employee identified by their employee ID.

PL/SQL Code:

CREATE OR REPLACE PROCEDURE calculate_first_name_ascii_sum(
p_employee_id IN employees.employee_id%TYPE
) AS
v_first_nameemployees.first_name%TYPE;
v_ascii_sum NUMBER := 0;
BEGIN
  SELECT first_name INTO v_first_name FROM employees WHERE employee_id = p_employee_id;

  FOR i IN 1..LENGTH(v_first_name) LOOP
v_ascii_sum := v_ascii_sum + ASCII(SUBSTR(v_first_name, i, 1));
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Sum of ASCII values for the first name: ' || v_ascii_sum);
END;
/
To execute the procedure:
DECLARE
v_employee_idemployees.employee_id%TYPE := 100; -- Provide the employee ID as needed
BEGIN
calculate_first_name_ascii_sum(p_employee_id =>v_employee_id); -- Call the procedure with the employee ID
END;
/

Sample Output:

Sum of ASCII values for the first name: 789

Explanation:

The said code in Oracle's PL/SQL defines a stored procedure that calculates the sum of ASCII values for the characters in the first name of an employee from the 'employees' table.

The procedure takes an input parameter "p_employee_id" that represents the employee ID.

Inside the procedure, two variables"v_first_name" and "v_ascii_sum" that declared to store the first name of the employee, and the sum of ASCII values.

The SELECT statement retrieves the first name of the employee from the "employees" table based on the provided employee ID assigned to the "v_first_name" variable.

A loop is then iterates through each character of the first name. The loop variable "i" ranges from 1 to the length of the first name. The SUBSTR function extracts each character at position "i" from the "v_first_name" variable, and the ASCII function returns the ASCII value of that character. The ASCII value is added to the "v_ascii_sum" variable.

Flowchart:

Exercise 3:

Write a PL/SQL function to determine whether a department name in the departments table contains any lowercase characters. Return 1 if it does, and 0 otherwise.

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

CREATE OR REPLACE FUNCTION is_lowercase_department(
p_department_id IN departments.department_id%TYPE
) RETURN NUMBER AS
v_department_namedepartments.department_name%TYPE;
BEGIN
  SELECT department_name INTO v_department_name FROM departments WHERE department_id = p_department_id;

  FOR i IN 1..LENGTH(v_department_name) LOOP
    IF ASCII(SUBSTR(v_department_name, i, 1)) BETWEEN 97 AND 122 THEN
      RETURN 1;
    END IF;
  END LOOP;

  RETURN 0;
END;
/
To execute the function:
SELECT is_lowercase_department(210) FROM dual;

Sample Output:

IS_LOWERCASE_DEPARTMENT(210)
------------------------------------------------------
1

Explanation:

The said code in Oracle's PL/SQL defines a function that determines whether a department name contains lowercase letters.

The function takes an input parameter "p_department_id" which represents the department ID for which the check should be performed.

The SELECT statement retrieves the department name from the "departments" table based on the provided department ID and assigned to the "v_department_name" variable.

A loop is then iterates through each character of the department name. The SUBSTR function extracts each character at position "i" from the "v_department_name" variable, and the ASCII function returns the ASCII value of that character.

An IF statement checks whether the ASCII value falls between 97 and 122, which represent the lowercase letters in the ASCII table. If a lowercase letter is found, the function immediately returns 1 to indicate that the department name contains lowercase letters.

If the loop completes without finding any lowercase letters, the function returns 0 to indicate that the department name does not contain lowercase letters.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: LENGTHB() Functions.
Next: CHR() Function.