Using the LOWER() and UPPER() Oracle Functions

Return rows for a column case insensitive

It is common practice to make a column case insensitive to ensure that you return all of the desired rows. 

SELECT *
FROM  COURSES
WHERE  LOWER(education_delivery_method)  = 'classroom' 

Display a column in uppercase

You can use either the Oracle UPPER() or LOWER() functions to format columns in your SQL SELECT.

SELECT 
  UPPER(FIRSTNAME) AS "FIRSTNAME",
  LASTNAME
FROM STUDENTS
WHERE RowNum < 11     

Format a column using Upper and Lower functions

It is possible to use the Oracle LOWER() and UPPER() functions in conjunction with the SUBSTRING() function to accomplish different types of formatting.

SELECT 
  UPPER(SUBSTR(LASTNAME,1,1)) || LOWER(SUBSTR(LASTNAME,2,29))
FROM STUDENTS
WHERE RowNum < 11

Update a group of rows changing case

It is possible to use the Oracle UPPER() or LOWER() functions in conjunction with an update statement to change the "case" of a group of rows.

UPDATE STUDENTS
SET    LASTNAME = UPPER(LASTNAME)
WHERE  LASTNAME  = 'Jones'  
/

COMMIT WORK ;
/

• Basic DML
  • SQL Select
  • SQL Update
  • SQL Insert
  • SQL Delete
• Functions
  • Substring function
  • Upper and Lower functions
  • RTRIM and LTRIM functions

Oracle Information and Resources