UPPER / LOWER / REPLACE

UPPER: Converts all characters in a string to uppercase.

UPPER(str)
UCASE(str)  -- Alias in MySQL

LOWER: Converts all characters in a string to lowercase.

LOWER(str)
LCASE(str)  -- Alias in MySQL

REPLACE: Replaces all occurrences of a specified substring with another string.

REPLACE(str, from_str, to_str)

Function	Description
UPPER(s)	Converts all alphabetic characters in the string to uppercase. Has no effect on non-Latin characters.
LOWER(s)	Converts all alphabetic characters in the string to lowercase. Has no effect on non-Latin characters.
REPLACE(s, from, to)	Replaces all occurrences of the search string in s with the replacement string. Case sensitivity depends on collation (MySQL).

The following examples use the customers table.

Normalize email addresses to lowercase to detect duplicates.

sample_upper_lower_replace.sql

SELECT
    LOWER(email) AS normalized_email,
    COUNT(*) AS count
FROM customers
GROUP BY LOWER(email)
HAVING COUNT(*) > 1;

+------------------------+-------+
| normalized_email       | count |
+------------------------+-------+
| ayanami.rei@example.com|     2 |
+------------------------+-------+
1 row in set

Convert email addresses to uppercase for display.

sample_upper_lower_replace.sql

SELECT
    email,
    UPPER(email) AS upper_email
FROM customers;

+------------------------+------------------------+
| email                  | upper_email            |
+------------------------+------------------------+
| Ayanami.Rei@Example.com| AYANAMI.REI@EXAMPLE.COM|
| ayanami.rei@example.com| AYANAMI.REI@EXAMPLE.COM|
| SHINJI@EXAMPLE.COM     | SHINJI@EXAMPLE.COM     |
+------------------------+------------------------+
3 rows in set

Replace the old domain with the new domain in bulk.

sample_upper_lower_replace.sql

UPDATE employees
SET email = REPLACE(email, 'old-company.com', 'new-company.com')
WHERE email LIKE '%@old-company.com';

Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

SELECT email FROM employees WHERE email LIKE '%@new-company.com';

+------------------------------+
| email                        |
+------------------------------+
| rei@new-company.com          |
| shinji@new-company.com       |
| asuka@new-company.com        |
| misato@new-company.com       |
| kaworu@new-company.com       |
+------------------------------+
5 rows in set

Normalize mixed-case domain names.

sample_upper_lower_replace.sql

UPDATE customers
SET email = REPLACE(email, 'Example.com', 'example.com')
WHERE email LIKE '%Example.com';

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT email FROM customers;

+-------------------------+
| email                   |
+-------------------------+
| Ayanami.Rei@example.com |
| ayanami.rei@example.com |
| SHINJI@EXAMPLE.COM      |
+-------------------------+
3 rows in set

MySQL / MariaDB

UPPER, LOWER, and REPLACE are available. MySQL also provides UCASE and LCASE as aliases, but use UPPER and LOWER for better portability.

SELECT UPPER(email), LOWER(email) FROM customers;
SELECT REPLACE(email, 'Example.com', 'example.com') FROM customers;

PostgreSQL

UPPER, LOWER, and REPLACE work the same as in MySQL.

SELECT UPPER(email), LOWER(email) FROM customers;
SELECT REPLACE(email, 'Example.com', 'example.com') FROM customers;

SQLite

UPPER, LOWER, and REPLACE are available in SQLite as well. Note that SQLite's UPPER and LOWER only work on ASCII alphabetic characters and have no effect on multibyte characters.

SELECT UPPER(email), LOWER(email) FROM customers;
SELECT REPLACE(email, 'Example.com', 'example.com') FROM customers;

UPPER and LOWER convert the case of alphabetic characters but have no effect on non-Latin characters. To perform a case-insensitive search, you can normalize values in the WHERE clause using UPPER or LOWER before comparing, or configure the database collation to be case-insensitive.

REPLACE substitutes all occurrences of the search string. In MySQL, whether REPLACE is case-sensitive depends on the collation in use. A binary collation performs exact matches only.

For string concatenation and length, see CONCAT / LENGTH. For extracting substrings and trimming whitespace, see SUBSTRING / TRIM.