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).

 

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 inital 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_firstname

You 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 Oracle 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!