UPPER(): Oracle PL/SQL upper function converting strings to uppercase

PL/SQL String Functions: UPPER()

Exercise 1:

Write a PL/SQL block that retrieves the first name and last name of employees in the employees table and converts them to uppercase. Display the uppercase names.

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
n_first_nameemployees.first_name%TYPE;
n_last_nameemployees.last_name%TYPE;  
v_first_nameemployees.first_name%TYPE;
v_last_nameemployees.last_name%TYPE;
BEGIN
  FOR emp IN (SELECT first_name, last_name FROM employees) LOOP
n_first_name := emp.first_name;
n_last_name := emp.last_name;
v_first_name := UPPER(emp.first_name);
v_last_name := UPPER(emp.last_name);
    DBMS_OUTPUT.PUT_LINE('First and Last Name: ' || n_first_name || n_last_name || 'In UPPER First and Last Name: ' || v_first_name || v_last_name);
  END LOOP;
END;

Sample Output:

First and Last Name: Kelly      Chung       In UPPER First and Last Name: KELLY      CHUNG       
First and Last Name: Jennifer   Dilly       In UPPER First and Last Name: JENNIFER   DILLY       
First and Last Name: Timothy    Gates       In UPPER First and Last Name: TIMOTHY    GATES       
First and Last Name: Randall    Perkins     In UPPER First and Last Name: RANDALL    PERKINS     
First and Last Name: Sarah      Bell        In UPPER First and Last Name: SARAH      BELL        
First and Last Name: Britney    Everett     In UPPER First and Last Name: BRITNEY    EVERETT     
First and Last Name: Samuel     McCain      In UPPER First and Last Name: SAMUEL     MCCAIN      
First and Last Name: Vance      Jones       In UPPER First and Last Name: VANCE      JONES       
First and Last Name: Alana      Walsh       In UPPER First and Last Name: ALANA      WALSH       
First and Last Name: Kevin      Feeney      In UPPER First and Last Name: KEVIN      FEENEY      
First and Last Name: Donald     OConnellIn UPPER First and Last Name: DONALD     OCONNELL    
First and Last Name: Douglas    Grant       In UPPER First and Last Name: DOUGLAS    GRANT       
First and Last Name: Jennifer   Whalen      In UPPER First and Last Name: JENNIFER   WHALEN
.......

Explanation:

The said code in Oracle's PL/SQL that retrieves the first_name and last_name values from the employees table and converts them to uppercase, and displays the original and uppercase versions of the names.

The variables n_first_name, n_last_name are of the same data type as the columns first_name and last_name in the 'employees' table and v_first_name, v_last_name also same datatype as previous are declared.

Inside the BEGIN block, a cursor selects the first_name and last_name columns from the employees table.

The each iteration of the FOR loop, the values of first_name and last_name obtained from the cursor are assigned to the n_first_name and n_last_name variables, respectively and the UPPER function converts the first_name and last_name values to uppercase and assigned to the v_first_name and v_last_name variables.

The DBMS_OUTPUT.PUT_LINE function display the values with the original first_name and last_name concatenated with their corresponding uppercase versions.

Flowchart:

Exercise 2:

Write a PL/SQL block that prompts the user to enter a string and converts it to uppercase using the UPPER function. Display the uppercase string.

PL/SQL Code:

DECLARE
v_input_stringVARCHAR2(100);
v_uppercase_stringVARCHAR2(100);
BEGIN
v_input_string := 'i_was_a_lowercase_and_transfered_to_uppercase';
v_uppercase_string := UPPER(v_input_string);
  DBMS_OUTPUT.PUT_LINE('Uppercase String: ' || v_uppercase_string);
END;

Sample Output:

Uppercase String: I_WAS_A_LOWERCASE_AND_TRANSFERED_TO_UPPERCASE

Explanation:

The said code in Oracle's PL/SQL that takes a predefined lowercase string, and converts it to uppercase.

The variable v_input_string and v_uppercase_string are of the VARCHAR2 data type with a length of 100 characters are declared.

The UPPER function converts the value 'i_was_a_lowercase_and_transfered_to_uppercase' stored in v_input_string to uppercase, and the result is assigned to the variable v_uppercase_string.

The DBMS_OUTPUT.PUT_LINE function displays the uppercase string by concatenating it with a descriptive text 'Uppercase String: '.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: TRIM() Functions.