Topic 39 of 52
LOWER, UPPER
Overview
LOWER and UPPER convert string case, essential for case-insensitive comparisons and consistent data formatting. The CASE expression (different from case conversion) adds conditional logic within SQL queries.
Syntax
sql
-- Case conversion functions
UPPER(email) -- 'PRIYA@EXAMPLE.COM'
LOWER(email) -- 'priya@example.com'
INITCAP('hello world') -- 'Hello World' (PostgreSQL)
-- Case-insensitive search using LOWER
WHERE LOWER(email) = LOWER(:search_term)
WHERE LOWER(name) LIKE LOWER('%' || :query || '%')
-- CASE expression (conditional logic)
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END
-- Simple CASE (like switch/case)
CASE status
WHEN 'active' THEN 'Active User'
WHEN 'inactive' THEN 'Inactive'
WHEN 'banned' THEN 'Suspended'
ELSE 'Unknown'
END
-- Searched CASE (boolean conditions)
CASE
WHEN age < 18 THEN 'Minor'
WHEN age < 60 THEN 'Adult'
ELSE 'Senior'
ENDCommon Pitfalls
- Using LOWER() on a column in WHERE prevents index usage — consider a case-insensitive index (citext in PostgreSQL) for frequently searched fields.
- CASE returns the first matching condition — order conditions from most to least specific.
- Interview tip: CASE WHEN expression inside aggregate functions (like SUM(CASE WHEN status='X' THEN 1 ELSE 0 END)) enables conditional aggregation — a very powerful pattern.
Real-World Example
Data normalization and conditional labeling for reporting:
example
sql
-- Product status report with case-based labels
SELECT
p.id,
INITCAP(LOWER(TRIM(p.name))) AS normalized_name,
UPPER(p.sku) AS sku,
p.price,
p.stock,
-- Stock status label
CASE
WHEN p.stock = 0 THEN 'Out of Stock'
WHEN p.stock < p.reorder_point THEN 'Low Stock'
WHEN p.stock > p.reorder_point * 5 THEN 'Overstock'
ELSE 'In Stock'
END AS stock_status,
-- Price tier
CASE
WHEN p.price < 500 THEN 'Budget'
WHEN p.price < 2000 THEN 'Mid-Range'
WHEN p.price < 10000 THEN 'Premium'
ELSE 'Luxury'
END AS price_tier,
-- Availability
CASE WHEN p.is_active AND p.stock > 0 THEN 'Available' ELSE 'Unavailable' END AS availability
FROM products p
ORDER BY normalized_name;