Course contentsShow
← Data Science
Lesson 862 of 2,14522. SQL FoundationsFree lesson

Case Sensitivity in Text Filtering

Understand how different databases handle case sensitivity and use UPPER/LOWER for consistent comparisons.

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.