SQL Functions   «Prev  Next»
Lesson 4 The UPPER and LOWER functions
Objective Understand the capabilities of the UPPER and LOWER functions.

Upper and Lower SQL Functions

UPPER SQL Function

One can use this function to convert your string to all uppercase letters. For example, you could change computer to COMPUTER.
This can help when you are working with the names of states and need to make sure the abbreviation provided by the user is uppercase.
The syntax is simple:

SELECT UPPER(State) 
FROM Authors

You will get back a single, unnamed column with all the state abbreviations converted to uppercase.

LOWER Function

As you probably guessed, the lower function will convert your string to all lowercase letters. For example, it will change DistributedNetworks to distributednetworks.
As with UPPER, the syntax is straightforward:

SELECT LOWER(State) 
FROM Authors

This returns a single, unnamed column with all the state abbreviations converted to lowercase.

Changing String Case with UPPER() and LOWER()

Use the function UPPER() to return a string with lowercase letters converted to uppercase, and use the function LOWER() to return a string with uppercase letters converted to lowercase. The functions' important characteristics are:
  1. A cased character is a letter that can be lowercase (a) or uppercase (A).
  2. Case changes affect only letters. Digits, punctuation, and whitespace are left unchanged.
  3. Case changes have no effect on empty strings (' ' ).
  4. If its argument is null, UPPER() and LOWER() return null.

Case-Insensitive Comparisons

In DBMSs that perform case-sensitive WHERE-clause comparisons by default, UPPER() or LOWER() often is used to make case-insensitive comparisons:
WHERE UPPER(au_fname) = 'JOHN'

If you are sure that your data are clean, it is faster to look for only reasonable letter combinations than to use case functions:
WHERE au_fname = 'JOHN'
OR au_fname ='John'

UPPER() and LOWER() affect characters such as accents and umlauts:
UPPER(‘ö’) is ‘Ö’, for example.
If your data contain such characters and you are making case-insensitive comparisons such as
WHERE UPPER(au_fname) = 'JOSE'

make sure that your DBMS does not lose the marks on conversion.
UPPER('José') should be 'JOSÉ', not 'JOSE'.

Upper Lower - Exercise

Go to the exercise link below to try running these queries.
Upper Lower Function - Exercise