How To Use Character Functions That Manipulate The Case

In the single-row functions series, we are going to talk about Character Functions, the ones that manipulate the case. Because repetition is the mother of skill, I am going to repeat myself again here, the single-row functions return a single result for every row, and they operate on one row at a time. Since character functions are single row functions, they also operate on one row at a time.

Character functions can return either a character data type, or numeric data type, however the functions we are talking about today, return character data types. These functions can be used in the SELECT, WHERE, START WITH and CONNECT BY, and HAVING clauses of a SELECT statement.

The character functions that manipulate case are: INITCAP, LOWER, UPPER. There are also similar NLS functions: NLS_INITCAP, NLS_LOWER, NLS_UPPER, these can take into consideration a named collation, which would handle special linguistic requirements for case conversion.

These functions convert the input character to the desired case, and return the same data type as your input character string.

The input character string can be a hard coded string like ‘Good Morning’, a table column like last_name, a bind variable :last_name, a substitution variable like &last_name, or the output of another function like substring(‘Diana’,1).

Mostly, these functions are used in the SELECT or WHERE clause of a SQL Statement. We either want to manipulate the case of the output (what we return) or we want to put a condition in the WHERE clause (especially used with bind variables and when you want to bullet proof your code).

As with any character strings, you might need to enclose the input in single quotes ‘ ‘. If the input character string is a column of a table, then you do not need to enclose it into single quotes. For all the other cases, you will need to enclose the input string into single quotes ”. See examples below.

Let’s have a look at the Case Manipulating Character Functions:

INITCAP(character_string)

This function converts the input character into words that have the first letter in uppercase, and the rest of the letters in lower case. Words are delimited by space or non-alphanumeric characters (think of special characters !&?#*…)
The input is a character datatype ( CHAR, VARCHAR2, NCHAR, NVARCHAR2), and the output has the same data type as the input character. You cannot use this function with CLOB directly, but they can be passed in as arguments through implicit data conversion.

select initcap('I love coffee!and cake') init_string from dual;

INIT_STRING
----------------------
I Love Coffee!And Cake

select last_name from hr.employees
where initcap(last_name)='Robete';

LAST_NAME
----------
Robete

select last_name from hr.employees
where initcap(last_name)='&last_name';

Enter value for last_name: Robete
...
LAST_NAME
----------
Robete

LOWER(character_string) and UPPER(character_string)

Both of these functions are similar, as in the input character has to be of a character data type: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB, and the return value is of the same data type as the input character.
The LOWER function returns the input character in lower case.
The UPPER function returns the input character in upper case.

select lower('Diana') lower_case, upper('Diana') upper_case from dual;

LOWER_CASE  UPPER_CASE
----------  ------------
diana       DIANA

select last_name, first_name
from hr.employees
where upper(last_name) = 'ROBETE';

LAST_NAME   FIRST_NAME
----------  ------------
Robete      Diana
 
select last_name, first_name
from hr.employees
where upper(last_name) = upper('&lname');

Enter value for lname: robete
...
LAST_NAME   FIRST_NAME
----------  ------------
Robete      Diana

Fun Fact: These functions can be used not only in SELECT statements, but also in INSERT/UPDATE/DELETE/MERGE statements!

update hr.employees
set last_name='Robete'
where upper(last_name)='ROBETE';

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $13.99 CAD !