Matching Uppercase and Lowercase Letters

Sometimes data is stored in uppercase letters (“COMPUTER”), sometimes in lowercase letters (“computer”), and sometimes in mixed cases (“Computer”). This may reflect the lack of standards when the data was entered and thus the absence of integrity checks.

For example, let's say you want to determine the exact title of a book that is called either Life Without Fear or Life without Fear. You're unsure of the case of the word without. In fact, you suspect that the data is inconsistent throughout and there is no good way to predict how book titles are stored. There are a number of ways to deal with this.

You can use LIKE, as in the following example, if your dialect allows you to mark sets of characters with square brackets—not all do:

select title
from titles
where title
    like '%[Ww][Ii][Tt][Hh][Oo][Uu][Tt]%'
title
===============================
Life Without Fear
[1 row]

Each bracketed pair allows matches on either upper- or lowercase letters.

Another option is to use the UPPER function. It converts lowercase letters to uppercase letters.

The syntax is this:

UPPER (char_expression)

Once the data has been changed to uppercase letters, LIKE compares it to the matching pattern:

select title
from titles
where upper(title) like '%WITHOUT%'
					

This is easier to read and understand than the LIKE syntax alone. UPPER's sister function, LOWER, works the same way but converts data to a lowercase string. Use UPPER (or LOWER) twice to match any combination of upper- and lowercase letters:

select title
from titles
where upper(title) like upper("%wiTHout%)
					

It may appear that the simplest answer to the problem of case is to enter the data in one case only and avoid this kind of work. But be careful! You may lose important information that you'll need later. Many names use both upper- and lowercase letters internally. Consider the names Blotchett-Halls, DeFrance, O'Leary, MacFeather, and del Castillo from the bookbiz database. You've probably seen them written with irregular distributions of capital letters. If you've stored them in a one-case style, you may find yourself trying to re-create distinctions you once had but then eliminated.