Clean & Normalize User Names (Foundational)

basic

Customer database has messy names: " JOHN DOE ", "jane SMITH", "bob". Need consistent format for display.

SELECT

customer_id,

TRIM(LOWER(first_name)) AS first_clean,

TRIM(LOWER(last_name)) AS last_clean,

CONCAT(

TRIM(LOWER(first_name)),

' ',

TRIM(LOWER(last_name))

) AS display_name

FROM customers;

customer_id | first_clean | last_clean | display_name

1 | john | doe | john doe

2 | jane | smith | jane smith

3 | bob | jones | bob jones

Nesting TRIM() and LOWER() cleans messy data. CONCAT() is safer than || for NULLs. Real-world lesson: data is always messy - normalize at query time or insert time.

All

Extract Email Domain (Pattern Extraction)

intermediate

Analyze which email providers customers use (Gmail, Yahoo, corporate) for marketing segmentation.

SELECT

email,

SUBSTRING(

email,

POSITION('@' IN email) + 1

) AS email_domain,

CASE

WHEN LOWER(email) LIKE '%@gmail.com' THEN 'Gmail'

WHEN LOWER(email) LIKE '%@yahoo.%' THEN 'Yahoo'

WHEN LOWER(email) LIKE '%@company.com' THEN 'Corporate'

ELSE 'Other'

END AS provider

FROM customers;

email | email_domain | provider

john@gmail.com | gmail.com | Gmail

jane@company.com | company.com | Corporate

bob@yahoo.co.uk | yahoo.co.uk | Yahoo

POSITION() finds @ location, SUBSTRING() extracts from that position. CASE with LIKE classifies providers. LOWER() enables case-insensitive matching.