UPPER / LOWER / REPLACE

-- UPPER: Converts all characters in a string to uppercase.
UPPER(string)
UCASE(string)  -- Alias in MySQL

-- LOWER: Converts all characters in a string to lowercase.
LOWER(string)
LCASE(string)  -- Alias in MySQL

-- REPLACE: Replaces all occurrences of a substring within a string.
REPLACE(string, search_string, replacement_string)

-- Normalize email addresses to lowercase to detect duplicates.
SELECT
    LOWER(email) AS normalized_email,
    COUNT(*) AS count
FROM customers
GROUP BY LOWER(email)
HAVING COUNT(*) > 1;

-- Normalize product codes to uppercase before searching.
SELECT product_name, unit_price
FROM products
WHERE UPPER(product_code) = UPPER('el0042');

-- Replace the old domain with the new domain in all email addresses.
UPDATE employees
SET email = REPLACE(email, 'old-company.com', 'new-company.com')
WHERE email LIKE '%@old-company.com';

-- Remove specific characters from product descriptions (replace full-width spaces with half-width).
SELECT
    product_name,
    REPLACE(description, '　', ' ') AS description_normalized
FROM products;

-- Duplicate email addresses detected.
normalized_email            | count
----------------------------+------
yamada.taro@example.com     | 2
suzuki@example.com          | 3

-- Verification after domain replacement.
name           | old_email                       | new_email
---------------+---------------------------------+---------------------------
Taro Yamada    | yamada@old-company.com          | yamada@new-company.com
Hanako Suzuki  | suzuki.h@old-company.com        | suzuki.h@new-company.com

'UPPER', 'LOWER', and 'REPLACE' are available in MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.

-- Syntax that works across all databases.
SELECT UPPER(product_code), LOWER(email) FROM customers;
SELECT REPLACE(email, 'old-company.com', 'new-company.com') FROM employees;

The MySQL aliases 'UCASE' and 'LCASE' are MySQL-specific and are not available in other databases. Use 'UPPER' and 'LOWER' for better portability.

'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'.