Case Sensitivity in Text Filtering
You'll learn how databases treat uppercase and lowercase letters when filtering text, and how to ensure consistent comparisons.
The Problem with Case Sensitivity
When you filter text data using SQL's WHERE clause, some databases treat 'Apple', 'apple', and 'APPLE' as different values, while others consider them identical. This inconsistency can cause confusion and missed results.
Think of it like a library filing system: some libraries might file "Smith" and "smith" separately, while others group them together. Both approaches work, but you need to know which one you're using!
Database-Specific Behavior
MySQL (default): Case-insensitive — WHERE name = 'John' matches "John", "JOHN", and "john"
PostgreSQL (default): Case-sensitive — WHERE name = 'John' only matches "John"
SQL Server: Often case-insensitive, but depends on collation settings
This means a query that works perfectly on one system might miss data on another.
The Universal Solution: UPPER() and LOWER()
To guarantee consistent results across all databases, convert both sides of your comparison to the same case:
-- Make everything uppercase for comparison
WHERE UPPER(name) = UPPER('john')
-- Returns: 'John', 'JOHN', 'john', 'JoHn', etc.
-- Or make everything lowercase
WHERE LOWER(email) LIKE LOWER('%ADMIN%')
-- Finds 'admin', 'Admin', 'ADMIN' in any email
This technique forces case-insensitive matching regardless of database configuration.
Key Takeaway: Different databases handle case sensitivity differently; always use UPPER() or LOWER() functions to ensure your text filters work consistently across platforms and return all relevant results.