Lesson 7.3
Cleaning and Reshaping Text in SQL
Real text data needs reshaping. Functions like lower, trim, left, and || build clean, consistent values.
Text rarely arrives in exactly the shape you need. SQL gives you small building blocks to fix that: lower() and upper() change case, trim() removes stray spaces, and left() grabs the first few characters.
The || operator glues strings together. Chain these and you can build a clean, consistent value out of messy parts.
Pattern
select product_id,
upper(trim(product_name)) as product_name_clean
from productsSchema · Garden ShopTable · customers9 columns · 20 rows
Table · customers
9 columns · 20 rowsOne row per customer. Some customers have no phone on file.
customer_id intfirst_name textlast_name textemail textphone textcity textstate textsignup_date dateis_active bool
Your task
Generate a login handle for each customer: the first letter of their first name joined to their fulllast name, all in lowercase. Return customer_id,first_name, last_name, and the handle aslogin. Sort by customer_id.
SQL Workbench
⌘↵ to run·
Expected answer
- Columns: customer_id, first_name, last_name, login.
- Rows: 20 customers.
- Theo Brandt becomes tbrandt; Owen Castellano becomes ocastellano.