Changing the case of text in Oracle is a common task when comparing strings or validating data. Learn how to do it with the UPPER, LOWER, and INITCAP functions in this article.
Purpose of the UPPER, LOWER, and INITCAP Functions
The Oracle UPPER function takes a string input value and converts the characters to uppercase versions of each character. In short, it capitalises a string value.
lower
It’s useful for comparing values that may have mixed case or to convert input values from user input to check or store in your database.
The LOWER function converts all letters within the specified string to lower case. It’s the opposite of the UPPER function.
The Oracle INITCAP function translates a specified string into another string which has the first letter of each word capitalised, and all other letters in lower case.
The words are separated by white space, or characters that are not alphanumeric (such as dashes, tabs, carriage returns, or periods).
Also, just like with any string in Oracle, if you want to use a literal value you’ll need to enclose it in single quotes (as you’ll see in the examples below). If you want to use a string that has quotes in it, you’ll need to “escape” it. You can learn what that means and how to do it here: How to Escape Single Quotes in SQL.
UPPER Function Syntax and Parameters
The syntax of the UPPER function is quite simple:
UPPER (input_string)
The return value is the same as the input_string value, which can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
The parameters are:
- input_string (mandatory): The string value to convert into upper case.
As mentioned above, the input_string can be any of several string-based data types.
If the input_string contains any non-alpha characters, they are unaffected. They will remain in the returned string without conversion.
LOWER Function Syntax and Parameters
The syntax for the Oracle LOWER function is:
LOWER ( input_string )
The parameters of the LOWER function are:
- input_string (mandatory): This is the string that will be converted to lower case.
If there are any non-letter characters within the input_string, such as numbers, they will be unaffected.
INITCAP Function Syntax and Parameters
The syntax of the INITCAP function is:
INITCAP ( input_string )
The parameters of the INITCAP function are:
- input_string (mandatory): This is the string that is converted to initial capital letters and the rest lowercase.
The input_string can be a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type.
The return type is the same as the type of the input_string.
Can You Use Oracle LOWER in the WHERE Clause?
Yes, you can use the LOWER function in a WHERE clause.
It works just like any other WHERE clause.
For example:
SELECT *
FROM student
WHERE LOWER(first_name) = 'john';
Can You Use Oracle UPPER In a WHERE Clause?
Yes, you can use the Oracle UPPER function in a WHERE clause.
To do the comparison, though, you’ll probably need to have both sides of the comparison use the UPPER function.
For example, this would only work if the input_firstname is always in upper case:
WHERE UPPER(firstname) = input_firstnameYou may need to do this, depending on your needs:
WHERE UPPER(firstname) = UPPER(input_firstname)See the Examples section below for more information on how to use this in a WHERE clause.
Can You Use Oracle UPPER with a LIKE Statement?
Yes, you can use LIKE with an UPPER function. You may need to use the UPPER function on both sides, though.
For example, this statement would work if you can specify the comparison value in all caps:
WHERE UPPER(firstname) LIKE ('JO%')If you can’t specify upper case, then you can use the UPPER function on both sides:
WHERE UPPER(firstname) LIKE UPPER('jo%')See the Examples section below for more information on how to use this.
Can You Use Oracle UPPER On The First Letter?
Yes, you can. It depends on how you want to capitalise the string.
If you want the first letter of every word capitalised, you can use INITCAP.
SELECT INITCAP('this is my sentence')
FROM dual;Result:
This Is My Sentence
If you want to capitalise only the first letter of the entire string, then you can use a combination of SUBSTR and UPPER.
SELECT UPPER(SUBSTR('your string here',1,1)) || SUBSTR('your string here',2)
FROM dual;See the Examples below on how to do this with some output.
What’s The Oracle UPPER Function Performance Like?
The performance of the UPPER function is not too bad, but the main concerns around performance are where the comparison is done on a database field that does not have the right index.
First of all, for UPPER functions in the where clause, you should be using UPPER on both sides in most situations.
Next, in Oracle, an index is case-sensitive, so a search for “Complete” will be different to a search for “COMPLETE”.
The most important factor is the index. If you have an index on the firstname column, for example, a WHERE condition that uses UPPER(firstname) will not use that index. Not using the index will cause the query to run slower.
See below for how to create an index using the Oracle UPPER function, or read this guide on SQL indexes.
Can You Create An Index Using the Oracle UPPER Function?
Yes, you can. It’s called a function-based index.
To allow the use of an index, you need to create a function-based index. You should create an index on this column using the UPPER of that column.
For example:
CREATE INDEX idx_upper_name
ON customers (UPPER(firstname));Now the query should run a lot better.
Examples of the LOWER Function
Here are some examples of the Oracle LOWER function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1
This is a simple example using the LOWER function on the word Saturday
SELECT LOWER('Saturday') AS lower_val
FROM dual;Result:
| LOWER_VAL |
| saturday |
It shows the entire word in lower case.
Example 2
This example shows a longer sentence as well as numbers. Note that there is a capital letter for Houston about halfway through the string.
SELECT LOWER('The city of Houston 2468') AS LOWER_VAL
FROM dual;Result:
| LOWER_VAL |
| the city of houston 2468 |
You can see the entire string is lowercase, not just the first letter. The numbers are unaffected.
Example 3
This example uses an input string which is entirely upper case.
SELECT LOWER('ENGLAND') AS LOWER_VAL
FROM dual;Result
| LOWER_VAL |
| england |
This shows the entire string has been converted from upper case to lower case.
Example 4
This example uses records from the database table.
SELECT
first_name,
LOWER(first_name) AS lower_fn
FROM student;Result:
| FIRST_NAME | LOWER_FN |
| Jason | jason |
| Joan | joan |
As you can see, all of the first_name records have been converted to lower case.
Examples of the Oracle UPPER Function
Here are some examples of the Oracle UPPER function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1
This example shows the basic use of an UPPER function with a simple string.
SELECT UPPER('Database Star’) AS upper_val
FROM dual;Result:
| UPPER_VAL |
| DATABASE STAR |
The string is converted to all-caps.
Example 2
This example uses a combination of numbers and characters.
SELECT UPPER('Database 2018') AS upper_val
FROM dual;Result:
| UPPER_VAL |
| DATABASE 2018 |
The string is converted to all-caps and the numbers are unchanged.
Example 3
This example uses an UPPER function in a WHERE clause, with the UPPER function on one side. It uses our sample database of customers.
SELECT first_name, last_name
FROM customers
WHERE UPPER(first_name) = 'JOHN';Result:
| FIRST_NAME | LAST_NAME |
| John | Smith |
This result shows a single record that matches the uppercase of John.
Example 4
This example uses the UPPER function on both sides of a WHERE clause.
SELECT first_name, last_name, country
FROM customers
WHERE UPPER(country) = UPPER('usa');Result:
| FIRST_NAME | LAST_NAME | COUNTRY |
| John | Smith | USA |
| Sally | Jones | USA |
| Adam | Cooper | USA |
This result shows three records where the country was matched.
Example 5
This example uses the UPPER function in the WHERE clause with the LIKE keyword.
SELECT first_name, last_name, country
FROM customers
WHERE UPPER(country) LIKE ('U%');Result:
| FIRST_NAME | LAST_NAME | COUNTRY |
| John | Smith | USA |
| Sally | Jones | USA |
| Mark | Allan | UK |
| Adam | Cooper | USA |
This result shows the matches for the country that starts with the letter “U”.
Example 6
This example uses the UPPER function on both sides of the WHERE clause with the LIKE keyword.
SELECT first_name, last_name, country
FROM customers
WHERE UPPER(first_name) LIKE UPPER('m%');Result:
| FIRST_NAME | LAST_NAME | COUNTRY |
| Mark | Allan | UK |
This result shows a single record, where the first name starts with “M”.
Example 7
This example converts a string to a string with only the first character capitalised.
SELECT
UPPER(SUBSTR('database star',1,1)) || SUBSTR('database star',2) AS upper_text
FROM dual;Result:
| UPPER_TEXT |
| Database star |
The query has converted the string to have only the first letter in upper case.
Examples of the INITCAP Function
Here are some examples of the INITCAP function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1
SELECT INITCAP('Once upon a time...') AS testvalue
FROM dual;Result:
| TESTVALUE |
| Once Upon A Time… |
Example 2
SELECT INITCAP('To BE or NOT TO BE') AS testvalue
FROM dual;Result:
| TESTVALUE |
| To Be Or Not To Be |
Example 3
SELECT INITCAP('this is the end') AS testvalue
FROM dual;Result:
| TESTVALUE |
| This Is The End |
Example 4
SELECT
INITCAP('WHAT IS THE TIME? I DO NOT KNOW') AS testvalue
FROM dual;Result:
| TESTVALUE |
| What Is The Time? I Do Not Know |
You can find a full list of Oracle SQL functions here.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!