SQL UPPER, LOWER, and INITCAP Function Guide, FAQ & Examples

Changing the case of text in SQL 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 SQL UPPER function converts a string to upper case. It takes a string input value and converts the characters to uppercase versions of each character. In short, it capitalises a string value.

The SQL LOWER function converts a string to lowercase. It’s the opposite of the UPPER function.

The INITCAP function capitalises the first letter of each word. It translates a specified string into another string that has the first letter of each word capitalised, and all other letters in lower case.

UPPER Function Syntax and Parameters

The syntax of the UPPER function is quite simple:

1UPPER (input_string)

The return value is the same as the input_string value, which can be any of CHAR, VARCHAR, VARCHAR2, NCHAR, VARCHAR, NVARCHAR2, CLOB, or NCLOB (depending on your database).

The parameters are:

• input_string (mandatory): The string value to convert into uppercase.

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:

1LOWER (input_string)

The parameters of the LOWER function are:

• input_string (mandatory): This is the string that will be converted to lowercase.

If there are any non-letter characters within the input_string, such as numbers, they will be unaffected.

INITCAP Function Syntax and Parameters

The INITCAP function only exists in Oracle and Postgres. If you want to capitalise the first letter in SQL Server or MySQL, you'll have to write your own function.

The syntax of the INITCAP function is:

1INITCAP (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, VARCHAR, VARCHAR2, NCHAR, NVARCHAR, or NVARCHAR2 data type.

The return type is the same as the type of the input_string.

The words are separated by white space, or characters that are not alphanumeric (such as dashes, tabs, carriage returns, or periods).

While you're here, if you want an easy-to-use list of the main features in SQL for different vendors, get my SQL Cheat Sheets here:

Common Questions for UPPER and LOWER Functions

Here are the answers to some questions you may be wondering about these functions.

Can You Use SQL 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:

1SELECT *
2FROM student
3WHERE LOWER(first_name) = 'john';

Can You Use SQL UPPER In a WHERE Clause?

Yes, you can use the SQL 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 uppercase:

1WHERE UPPER(firstname) = input_firstname

You may need to do this, depending on your needs:

1WHERE 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 SQL 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:

1WHERE UPPER(firstname) LIKE ('JO%')

If you can’t specify uppercase, then you can use the UPPER function on both sides:

1WHERE UPPER(firstname) LIKE UPPER('jo%')

See the Examples section below for more information on how to use this.

Can You Use SQL 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 (and you are using either Oracle or Postgres), you can use INITCAP.

1SELECT
2INITCAP('this is my sentence');

Result:

1This 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.

1SELECT
2UPPER(SUBSTR('your string here',1,1)) || SUBSTR('your string here',2);

See the Examples below on how to do this with some output.

What’s The SQL 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 an SQL database, an index is case-sensitive, so a search for “John” will be different to a search for “JOHN”.

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 SQL UPPER function, or read this guide on SQL indexes.

Also, just like with any string in SQL, 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.

Can You Create An Index Using the 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:

1CREATE INDEX idx_upper_name
2ON customers (UPPER(firstname));

Now the query should run a lot better.

Examples of the LOWER Function

Here are some examples of the SQL LOWER function.

Example 1

This is a simple example using the LOWER function on the word Saturday

1SELECT LOWER('Saturday');

Result:

1saturday

It shows the entire word in lowercase.

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.

1SELECT LOWER('The city of Houston 2468');

Result:

1the 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 that is entirely upper case.

1SELECT LOWER('ENGLAND');

Result

1england

This shows the entire string has been converted from uppercase to lowercase.

Example 4

This example uses records from the database table.

1SELECT
2first_name,
3LOWER(first_name) AS lower_fn
4FROM student;

Result:

As you can see, all of the first_name records have been converted to lowercase.

Examples of the UPPER Function

Here are some examples of the SQL UPPER function.

Example 1

This example shows the basic use of an UPPER function with a simple string.

1SELECT UPPER('Database Star’);

Result:

1DATABASE STAR

The string is converted to all caps.

Example 2

This example uses a combination of numbers and characters.

1SELECT UPPER('Database 123');

Result:

1DATABASE 123

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.

1SELECT first_name, last_name
2FROM customers
3WHERE UPPER(first_name) = 'JOHN';

Result:

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.

1SELECT first_name, last_name, country
2FROM customers
3WHERE UPPER(country) = UPPER('usa');

Result:

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.

1SELECT first_name, last_name, country
2FROM customers
3WHERE UPPER(country) LIKE ('U%');

Result:

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.

1SELECT first_name, last_name, country
2FROM customers
3WHERE UPPER(first_name) LIKE UPPER('m%');

Result:

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.

1SELECT
2UPPER(SUBSTR('database star',1,1)) || SUBSTR('database star',2);

Result:

1Database star

The query has converted the string to have only the first letter in uppercase.

Examples of the INITCAP Function

Here are some examples of the INITCAP function. This function only works in Oracle and Postgres.

Example 1

1SELECT INITCAP('Once upon a time...');

Result:

1Once Upon A Time…

The first letter of each word is capitalised here, even the "A".

Example 2

1SELECT INITCAP('To BE or NOT TO BE');

Result:

To Be Or Not To Be

Once again, the first letter of each word is capitalised.

Example 3

1SELECT INITCAP('this is the end');

Result:

1This Is The End

Example 4

1SELECT
2INITCAP('WHAT IS THE TIME? I DO NOT KNOW');

Result:

1What Is The Time? I Do Not Know

You can find a full list of Oracle SQL functions here.

While you're here, if you want an easy-to-use list of the main features in SQL for different vendors, get my SQL Cheat Sheets here: