Describe the Upper and Lower SQL Functions

The UPPER and LOWER functions are two of the most practical string functions in SQL. They perform a simple task—changing the case of alphabetic characters—but that simple task solves several common problems in database work. Developers use these functions when they want to standardize text for display, normalize user input, support more reliable comparisons, and reduce confusion caused by inconsistent capitalization in stored data.

Suppose one user enters a state abbreviation as mi, another enters MI, and another enters Mi. All three values may refer to the same state, but the strings are not identical in appearance. That difference matters when data is being displayed in reports or compared in a WHERE clause. The UPPER and LOWER functions help bring those values into a predictable form so the query result becomes easier to read and easier to work with.

This lesson explains what each function does, how each function is used in SQL queries, and why case conversion is useful in real database applications. It also shows where care is needed, especially when comparisons involve mixed-case data, accented characters, collations, or performance-sensitive search conditions.

The UPPER function takes a character expression and converts its alphabetic characters to uppercase. If a column contains words, abbreviations, or names in mixed case, UPPER returns those values in uppercase form for the result set.

A simple example is shown below:

SELECT UPPER(State)
FROM Authors;

If the State column contains values such as mi, Mi, or mI, the query returns those values as MI. This can be useful when the output needs to follow a consistent style. State abbreviations, department codes, status values, and short identifiers are often easier to read when presented in uppercase.

It is important to understand that this query changes the returned value in the result set, not the stored value in the underlying table. In other words, SELECT UPPER(State) formats what you see in the query output. It does not permanently rewrite the data in the Authors table. To make permanent changes, you would need an UPDATE statement.

The LOWER function performs the opposite transformation. It takes a character expression and converts its alphabetic characters to lowercase. This can be helpful when the output should be presented in lowercase or when a comparison must be normalized to a consistent lowercase form.

SELECT LOWER(State)
FROM Authors;

If the column contains MI, Mi, or mI, the query returns mi. As with UPPER, the transformation affects the query result, not the stored data itself. A function such as LOWER is often used when building predictable string comparisons or preparing text for presentation in a specific format.

Although UPPER and LOWER are easy to learn, they are not trivial. They form part of a broader data-normalization strategy in SQL. The more inconsistent user input becomes, the more valuable it is to have functions that can standardize what the query compares or returns.

When SQL applies UPPER or LOWER to a value, it changes only alphabetic characters that have uppercase and lowercase forms. Several important characteristics follow from that behavior:

1. A cased character is a letter that can appear in uppercase or lowercase form.
2. Case conversion affects letters, but digits, punctuation, and whitespace remain unchanged.
3. An empty string or blank-looking value is not made more meaningful by applying case conversion.
4. If the argument passed to UPPER or LOWER is NULL, the returned value is also NULL.

This null behavior is worth remembering. SQL functions typically preserve null semantics unless a function is explicitly designed to replace or coerce missing values. So if a row contains no value for a given character column, applying UPPER or LOWER does not invent one.

You can also apply these functions to literals and expressions, not only to column names. That means they can be used in simple test queries, concatenated expressions, aliased projections, and filter conditions.

One of the most common uses of these functions is output formatting. Even when data was entered inconsistently, the result set can still be presented in a consistent form. A developer might want all state abbreviations in uppercase, all usernames in lowercase, or a standard representation of category labels for export or reporting.

For example, a query might return uppercase values for easier reading:

SELECT UPPER(State) AS state_upper
FROM Authors;

The alias makes the output more readable by giving the returned expression a meaningful name. Without an alias, the query may produce an unnamed or system-labeled expression column. This is a small detail, but it contributes to cleaner and more professional query output.

When developers prepare reports, dashboards, flat-file exports, or user-facing screens, formatting consistency matters. Two values that logically refer to the same thing should not appear to be different simply because one was entered in uppercase and another in mixed case. Functions such as UPPER and LOWER help correct that problem at query time.

The most important practical use of UPPER and LOWER appears in filtering logic. In some database systems, string comparisons may be case-sensitive depending on the collation or comparison settings. When that happens, searching for a specific value can miss rows that contain the same letters in a different case.

A common pattern is to normalize one side of the comparison:

WHERE UPPER(au_fname) = 'JOHN'

This expression converts the value of au_fname to uppercase before the comparison takes place. If the underlying data contains John, john, or JOHN, they are all converted to JOHN for comparison purposes. That makes the filter more predictable when data entry is inconsistent.

The same idea can be expressed with LOWER:

WHERE LOWER(au_fname) = 'john'

Both approaches aim at the same goal: compare like with like. If the database stores text in mixed case and you cannot assume consistency, case normalization in the predicate may reduce comparison errors.

If the stored data is already clean and follows strict conventions, a developer may decide not to wrap the column in a case-conversion function. For example, if the system guarantees that first names are stored only as John-style proper-case values or only as uppercase values, a direct comparison may be enough.

WHERE au_fname = 'JOHN'
OR au_fname = 'John'

This style may be faster in some environments because the database does not need to apply a function to every row before testing the predicate. However, this approach assumes you know the shape of the data and that the data remains consistent over time. In many real systems, especially older systems or systems with many data-entry paths, that assumption is too optimistic.

So the design question becomes practical rather than theoretical: is the priority strict correctness with inconsistent data, or maximum efficiency with already normalized data? In production environments, the right answer depends on data quality, indexing strategy, collation rules, and the expected workload.

There is an important design caution when using UPPER or LOWER in a WHERE clause. Wrapping a column in a function may reduce the database optimizer’s ability to use an ordinary index on that column efficiently. The query can still be logically correct, but it may become more expensive to execute.

For example, this condition is easy to read:

WHERE UPPER(au_fname) = 'JOHN'

But if au_fname is indexed, some systems cannot use that standard index as effectively because the comparison is no longer against the raw column value. That does not mean the pattern is wrong. It means the developer should understand its trade-offs.

Depending on the database platform, alternatives may include:

• using a case-insensitive collation,
• storing normalized values in a consistent format,
• using a generated or computed column,
• creating a function-based or expression index where supported.

This lesson does not require deep optimizer theory, but it is useful to know that function-wrapped predicates can influence performance as well as correctness.

Case conversion becomes more interesting when character data includes accents, umlauts, or other locale-sensitive letters. The legacy lesson already hints at this with examples involving ö and José. That is a valuable point and should be preserved.

For example, the uppercase version of ö should be Ö. Likewise, a name such as José should ideally become JOSÉ, not JOSE. If the accent is lost, the transformation changes the spelling of the name, not just its case.

That means developers should not assume that every database handles every language and character set the same way. Actual behavior can depend on:

• database collation settings,
• locale rules,
• character encoding,
• Unicode support,
• vendor-specific implementation details.

If an application stores international names, city values, or multilingual text, it is wise to test case-conversion behavior directly in the target DBMS rather than relying on assumptions. A function that behaves correctly for plain ASCII text may produce unexpected results when the data includes accented characters.

A useful way to think about UPPER and LOWER is to treat them as part of string normalization. Normalization in this context means bringing textual values into a predictable and consistent representation. This is especially helpful when databases receive input from forms, imports, external systems, or manual entry.

Name fields, state codes, city names, product categories, status labels, and short text identifiers are all vulnerable to inconsistent capitalization. A normalized comparison can reduce confusion, while a normalized display can improve readability. That is why case-conversion functions appear so often in practical SQL work even though their syntax is simple.

They also connect naturally to neighboring SQL topics such as substring functions, concatenation, trimming, aliases, collation behavior, and data-quality review. In that sense, this lesson is not just about two isolated functions. It is also about how developers think carefully about textual data in relational systems.

By this point, you should understand the basic role of the UPPER and LOWER functions. You have seen that they can standardize output, normalize comparison behavior, and help compensate for mixed-case input. You have also seen that case conversion affects only letters, preserves NULL as NULL, and requires extra care when character data contains accents or locale-sensitive letters.

As you work through the exercise, focus on three questions:

1. What does the function do to the returned string value?
2. How does the result change when you use the function in a SELECT list versus a WHERE clause?
3. Why might a developer choose case normalization even when the syntax looks simple?

Those questions will help you move beyond memorizing syntax and toward understanding how the functions support real SQL problem solving.

Go to the exercise link below to try running these queries.