Course contentsShow
← Data Science
Lesson 1045 of 2,14526. Advanced SQLFree lesson

Case Conversion and Trimming Strings

Using UPPER, LOWER, INITCAP, TRIM, LTRIM, and RTRIM to clean and standardize text data.

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.