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:

UPPER (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:

LOWER (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:

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, 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:

SELECT *
FROM student
WHERE 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:

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 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:

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

If you can’t specify uppercase, 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 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.

SELECT
INITCAP('this is my sentence');

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

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:

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 SQL LOWER function.

Example 1

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

SELECT LOWER('Saturday');

Result:

saturday

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.

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

Result:

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

SELECT LOWER('ENGLAND');

Result

england

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

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

SELECT UPPER('Database Star’);

Result:

DATABASE STAR

The string is converted to all caps.

Example 2

This example uses a combination of numbers and characters.

SELECT UPPER('Database 123');

Result:

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

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

Result:

Database 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

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

Result:

Once Upon A Time…

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

Example 2

SELECT 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

SELECT INITCAP('this is the end');

Result:

This Is The End

Example 4

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

Result:

What 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: