Case Conversion and Trimming Strings
What You'll Learn
You'll learn how to standardize and clean messy text data by changing letter case and removing unwanted whitespace using SQL's built-in string functions.
Why This Matters
Real-world data is rarely perfect. Names might appear as "JOHN", "john", or "John". Email addresses might have extra spaces like " user@email.com ". Before you can analyze or compare text data reliably, you need to standardize it.
Core String Cleaning Functions
Case Conversion Functions
UPPER(string) converts all letters to uppercase:
SELECT UPPER('Sarah Jones') -- Returns 'SARAH JONES'
LOWER(string) converts all letters to lowercase:
SELECT LOWER('Sarah Jones') -- Returns 'sarah jones'
INITCAP(string) capitalizes the first letter of each word (not available in all SQL dialects):
SELECT INITCAP('sarah jones') -- Returns 'Sarah Jones'
Whitespace Trimming Functions
TRIM(string) removes spaces from both ends:
SELECT TRIM(' hello ') -- Returns 'hello'
LTRIM(string) removes spaces from the left (beginning):
SELECT LTRIM(' hello ') -- Returns 'hello '
RTRIM(string) removes spaces from the right (end):
SELECT RTRIM(' hello ') -- Returns ' hello'
Practical Cleaning Example
SELECT
TRIM(LOWER(email)) AS clean_email,
INITCAP(TRIM(customer_name)) AS formatted_name
FROM customers
WHERE UPPER(status) = 'ACTIVE';
This query standardizes emails to lowercase with no whitespace and formats names consistently, while ensuring case-insensitive status filtering.
Key Takeaway: Use UPPER, LOWER, and INITCAP to standardize case, and TRIM, LTRIM, RTRIM to remove unwanted whitespace—essential steps for reliable text comparisons and clean data presentation.