CHR(): Oracle PL/SQL code examples for ASCII characters

PL/SQL String Functions: CHR()

Exercise 1:

Write a PL/SQL block to display the character representation of each ASCII value in the range of 65 to 90.

Sample Solution:

PL/SQL Code:

DECLARE
v_character CHAR(1);
BEGIN
  FOR i IN 65..90 LOOP
v_character := CHR(i);
    DBMS_OUTPUT.PUT_LINE('Character for ASCII value ' || i || ': ' || v_character);
  END LOOP;
END;
/

Sample Output:

Character for ASCII value 65: A
Character for ASCII value 66: B
Character for ASCII value 67: C
Character for ASCII value 68: D
Character for ASCII value 69: E
Character for ASCII value 70: F
Character for ASCII value 71: G
Character for ASCII value 72: H
Character for ASCII value 73: I
Character for ASCII value 74: J
Character for ASCII value 75: K
Character for ASCII value 76: L
Character for ASCII value 77: M
Character for ASCII value 78: N
Character for ASCII value 79: O
.....

Explanation:

The said code in Oracle's PL/SQL that displays the characters corresponding to ASCII values from 65 to 90.

The DECLARE keyword declares a variable v_character of type CHAR(1).

Inside the BEGIN block, a FOR loop iterates over the range of ASCII values from 65 to 90. For each value, the CHR function converts the ASCII value to its corresponding character, which is then assigned to the v_character variable.

The DBMS_OUTPUT.PUT_LINE statement is prints the ASCII value and its corresponding character on a new line.

Flowchart:

Exercise 2:

Create a PL/SQL procedure to calculate the number of employees in the employees table whose last names start with a specific character provided as input. Use the CHAR function to compare the first character of each last name.

PL/SQL Code:

CREATE OR REPLACE PROCEDURE count_employees_by_initial(
p_initial IN VARCHAR2
) AS
v_count NUMBER := 0;
BEGIN
  FOR emp IN (SELECT last_name FROM employees) LOOP
    IF ASCII(SUBSTR(emp.last_name, 1, 1)) = ASCII(p_initial) THEN
v_count := v_count + 1;
    END IF;
  END LOOP;
    DBMS_OUTPUT.PUT_LINE('Number of employees with last name starting with ' || p_initial || ': ' || v_count);
END;
/
To execute the procedure:
DECLARE
v_initialVARCHAR2(1) := 'M'; -- Provide the desired initial here
BEGIN
count_employees_by_initial(p_initial =>v_initial); -- Call the procedure with the initial
END;
/

Sample Output:

Number of employees with last name starting with M: 10

Explanation:

The said code in Oracle's PL/SQL defines a procedure that counts the number of employees whose last names start with a given initial.

The code that takes an input parameter p_initial of type VARCHAR2.

Inside the procedure, a variable v_count of type NUMBER is declared and initialized to 0.

Inside the code the FOR loop iterates over each row in the 'employees' table, specifically retrieving the "last_name" column.

Within the loop, it checks whether the first character of the employee's last name that retrieves by using the SUBSTR() has the same ASCII value as the input parameter p_initial. If they match, the v_count variable is incremented by 1.

After the loop, the procedure outputs the number of employees whose last names start with the specified initial using DBMS_OUTPUT.PUT_LINE.

Flowchart:

Exercise 3:

Write a PL/SQL function to determine whether a job title in the employees table contains any non-alphabetic characters. Return 1 if it does, and 0 otherwise. Use the CHAR function to validate each character.

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 has_non_alphabetic_characters(
p_employee_id IN employees.employee_id%TYPE
) RETURN NUMBER AS
v_job_titleemployees.job_id%TYPE;
v_char CHAR(1);
BEGIN
  SELECT job_id INTO v_job_title FROM employees WHERE employee_id = p_employee_id;

  FOR i IN 1..LENGTH(v_job_title) LOOP
v_char := UPPER(SUBSTR(v_job_title, i, 1));

    IF NOT (v_char BETWEEN 'A' AND 'Z') THEN
      RETURN 1;
    END IF;
  END LOOP;

  RETURN 0;
END;
/
To execute the function:
DECLARE
v_result NUMBER;
BEGIN
  -- Call the function and store the result in the variable v_result
v_result := has_non_alphabetic_characters(p_employee_id => 123); -- Replace 123 with the desired employee ID

  -- Do something with the result
  IF v_result = 1 THEN
    DBMS_OUTPUT.PUT_LINE('The job title contains non-alphabetic characters.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('The job title contains only alphabetic characters.');
  END IF;
END;

Sample Output:

The job title contains non-alphabetic characters

Explanation:

The said code in Oracle's PL/SQL defines a function that checks if a given employee's job title contains any non-alphabetic characters and returns 1 if it does, or 0 if all characters are alphabetic.

The code that takes an input parameter p_employee_id of the same data type as the "employee_id" column in the 'employees' table.

Inside the function, two variables v_job_title and v_char are declares.

The code performs a SELECT statement that retrieves the "job_id" value associated with the given employee ID and stores it in the v_job_title variable.

The FOR loop that iterates over each character in the v_job_title string. The UPPER function converts the character to uppercase, and it is assigned to the v_char variable.

Within the loop, the IF statement checks whether the v_char variable falls outside the range of 'A' to 'Z', indicating that it is not an alphabetic character. If this condition is true, the function immediately returns 1.

If no non-alphabetic characters are found during the loop, the function returns 0.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: ASCII() Functions.
Next: LENGTH() Function.