String Functions (UPPER, LOWER)

---

title: “String Functions (UPPER, LOWER)” part: 1 topic_number: 20 slug: “string-functions-upper-lower” difficulty: “Beginner” prerequisites: “select-from-where” —

String Functions (UPPER, LOWER)

What Is It?

String functions manipulate text data. UPPER() converts text to uppercase, LOWER() converts to lowercase. They’re perfect for normalizing data, making case-insensitive comparisons, and formatting output.

Real-world analogy: Like the “Caps Lock” key — UPPER() turns everything to capitals, LOWER() turns everything to lowercase.

---

Basic Syntax

UPPER(column_or_string)  -- Converts to UPPERCASE
LOWER(column_or_string)  -- Converts to lowercase

Key points:

• Takes a string argument (column name or literal string)
• Returns a new string (doesn’t modify the original data in the database)
• Works on any text data (VARCHAR, TEXT, CHAR)

---

Basic Examples

Convert Names to Uppercase

SELECT 
    first_name,
    UPPER(first_name) AS first_name_upper,
    last_name,
    UPPER(last_name) AS last_name_upper
FROM employees
LIMIT 5;

Expected output:

first_name	first_name_upper	last_name	last_name_upper
Alice	ALICE	Johnson	JOHNSON
Bob	BOB	Smith	SMITH
Carol	CAROL	Williams	WILLIAMS
David	DAVID	Brown	BROWN
Eve	EVE	Davis	DAVIS

The original data is unchanged — we’re just transforming the display.

Convert to Lowercase

SELECT 
    department_name,
    LOWER(department_name) AS department_lower
FROM departments
LIMIT 5;

Expected output:

department_name	department_lower
Engineering	engineering
Marketing	marketing
Sales	sales
Human Resources	human resources
Finance	finance

---

Going Deeper

Case-Insensitive Searching (One Use Case)

Find employees whose name starts with “a”, case-insensitive:

SELECT first_name, last_name
FROM employees
WHERE LOWER(first_name) LIKE 'a%';

Expected output:

first_name	last_name
Alice	Johnson

How it works: LOWER() converts “Alice” to “alice”, then LIKE 'a%' matches.

Note: In MySQL, LIKE is case-insensitive by default, so this example is actually redundant! But in some databases (PostgreSQL, for example), you’d need this technique.

Normalizing Input for Comparison

Compare user input (which might be any case) to database values:

-- Imagine a user types "engineering" (lowercase)
SELECT * 
FROM departments
WHERE LOWER(department_name) = LOWER('Engineering');

What this does: Converts both sides to lowercase, so “Engineering”, “engineering”, “ENGINEERING” all match.

Returns: The Engineering department.

Combining UPPER/LOWER with CONCAT

Format names as “LAST, First”:

SELECT 
    CONCAT(UPPER(last_name), ', ', first_name) AS formatted_name
FROM employees
ORDER BY last_name
LIMIT 5;

Expected output:

formatted_name
ANDERSON, Jack
BROWN, David
CLARK, Sam
DAVIS, Eve
GARCIA, Paul

Professional looking output with last names emphasized in caps.

Pause and Predict: What does UPPER(NULL) return?

Answer

`NULL` All string functions return NULL if the input is NULL. This is the "NULL poison" effect we learned about in NULL handling.

---

Watch Out — Common Mistakes

Mistake #1: Thinking UPPER/LOWER Changes the Database

SELECT UPPER(first_name) FROM employees;

What beginners think: “This changes all first names to uppercase in the database.”

What actually happens: The query DISPLAYS names in uppercase, but the database is unchanged.

To actually change the database:

UPDATE employees
SET first_name = UPPER(first_name);

This permanently changes the data. Be very careful!

Mistake #2: Using UPPER/LOWER in WHERE Unnecessarily (Performance Issue)

--  SLOWER on large tables
SELECT * FROM employees
WHERE UPPER(first_name) = 'ALICE';

Why it’s slower: MySQL must convert every first_name to uppercase before comparing. On large tables, this prevents index usage.

Better:

-- • FASTER (MySQL LIKE is case-insensitive by default)
SELECT * FROM employees
WHERE first_name = 'Alice';

When UPPER/LOWER is needed: When you genuinely need case normalization, or when working with databases where comparisons are case-sensitive.

Mistake #3: Forgetting Mixed Case Exists

--  INCOMPLETE
SELECT * FROM employees
WHERE first_name = 'alice' OR first_name = 'ALICE';

What about: ‘Alice’, ‘ALice’, ‘AlIcE’, etc.?

Better:

-- • CORRECT
SELECT * FROM employees
WHERE LOWER(first_name) = 'alice';

This catches all case variations with one comparison.

---

Edge Case Spotlight

Non-ASCII Characters

UPPER() and LOWER() work with international characters:

SELECT 
    'café' AS original,
    UPPER('café') AS upper_case,
    LOWER('CAFÉ') AS lower_case;

Expected output:

original	upper_case	lower_case
café	CAFÉ	café

The é is handled correctly! MySQL’s character set (utf8mb4) supports international characters.

But be aware: Some special characters (like German ß) have unique uppercase rules. MySQL handles most cases correctly, but edge cases exist.

---

Try This

Exercise 1 (Guided)

Display all department names in lowercase, sorted alphabetically.

Hint

SELECT LOWER(department_name), ORDER BY the result.

Exercise 2 (Independent)

Find all employees whose last name contains the letter “A” or “a” (case-insensitive). Show their full name.

Hint

Use LOWER() or UPPER() with LIKE and the % wildcard.

Exercise 3 (Challenge)

Create a formatted email address for each employee: [email protected], all lowercase. Show first_name, last_name, and the generated email.

Hint

Use LOWER() and CONCAT(). The format is: LOWER(first_name) + '.' + LOWER(last_name) + '@company.com'

---

Answer Key

Exercise 1 Answer

```sql SELECT LOWER(department_name) AS department_name_lower FROM departments ORDER BY department_name_lower; ``` **Or ORDER BY the original column:** ```sql SELECT LOWER(department_name) AS department_name_lower FROM departments ORDER BY department_name; ``` **Expected output:** | department_name_lower | |-----------------------| | business development | | customer support | | data science | | engineering | | finance | | human resources | | legal | | marketing | | ... | All in lowercase, alphabetical order.

Exercise 2 Answer

```sql SELECT first_name, last_name FROM employees WHERE LOWER(last_name) LIKE '%a%'; ``` **Or using UPPER:** ```sql SELECT first_name, last_name FROM employees WHERE UPPER(last_name) LIKE '%A%'; ``` **Expected output (partial):** | first_name | last_name | |------------|-----------| | Eve | Davis | | Paul | Garcia | | Noah | Harris | | Leo | Jackson | | Quinn | Martinez | | Olivia | Martin | | Grace | Taylor | All employees whose last name contains "a" or "A". **Note:** In MySQL, LIKE is case-insensitive by default, so `WHERE last_name LIKE '%a%'` works without LOWER/UPPER. But using LOWER/UPPER makes your intent explicit and works across all databases.

Exercise 3 Answer

```sql SELECT first_name, last_name, CONCAT(LOWER(first_name), '.', LOWER(last_name), '@company.com') AS email FROM employees; ``` **Expected output (partial):** | first_name | last_name | email | |------------|-----------|-------| | Alice | Johnson | [email protected] | | Bob | Smith | [email protected] | | Carol | Williams | [email protected] | | David | Brown | [email protected] | **Professional email addresses generated from names!**

---

Quick Recap

• UPPER() converts text to uppercase
• LOWER() converts text to lowercase
• These functions don’t change the database — only the display
• Useful for case-insensitive comparisons and data normalization
• Work with international characters (utf8mb4)
• Return NULL if input is NULL
• Be cautious about performance on large tables (indexed columns)

---

Part 1 Complete!

Congratulations! You’ve finished all 20 topics in Part 1 — Foundations. You can now:

• Insert, update, delete data
• Create, alter, drop tables
• Query with SELECT, WHERE, ORDER BY, LIMIT
• Use aggregate functions and GROUP BY
• Understand primary and foreign keys
• Join tables (INNER, LEFT, RIGHT)
• Handle NULLs and duplicates
• Manipulate strings

You have a solid SQL foundation!

---

Up Next

Next topic: SUBSTRING → part2_01_substring.md

Ready for Part 2 — Intermediate techniques? Type ‘next’ to continue!