TRIM, LTRIM, RTRIM in SQL - Mini Project: Build & Apply

Using TRIM, LTRIM, and RTRIM to Clean Text Data

📖 Scenario: You work in a company database where customer names have extra spaces at the start or end due to inconsistent data entry. These spaces cause problems when searching or sorting names.

🎯 Goal: You will create a table with customer names that include extra spaces, then write queries using TRIM, LTRIM, and RTRIM functions to clean the names by removing unwanted spaces.

📋 What You'll Learn

Create a table called customers with a column name containing names with extra spaces

Insert specific customer names with leading and trailing spaces

Write a query using LTRIM to remove leading spaces

Write a query using RTRIM to remove trailing spaces

Write a query using TRIM to remove both leading and trailing spaces

💡 Why This Matters

🌍 Real World

Cleaning text data in databases is common when data comes from user input or external sources with inconsistent formatting.

💼 Career

Database administrators and developers often need to clean and standardize text data to ensure accurate queries and reports.

1

Create the customers table and insert data

Create a table called customers with a column name of type VARCHAR(50). Insert these exact names with spaces: ' Alice', 'Bob ', ' Charlie '.

SQL

-- Create the customers table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add the names exactly as given.

2

Add a query to remove leading spaces with LTRIM

Write a SELECT query that returns the name column from customers but removes leading spaces using the LTRIM function. Name the output column name_no_leading.

SQL

CREATE TABLE customers (name VARCHAR(50));
INSERT INTO customers (name) VALUES ('  Alice'), ('Bob  '), ('  Charlie  ');
-- Write SELECT query using LTRIM here

Need a hint?

Use SELECT LTRIM(name) AS name_no_leading FROM customers; to remove spaces on the left side.

3

Add a query to remove trailing spaces with RTRIM

Write a SELECT query that returns the name column from customers but removes trailing spaces using the RTRIM function. Name the output column name_no_trailing.

SQL

CREATE TABLE customers (name VARCHAR(50));
INSERT INTO customers (name) VALUES ('  Alice'), ('Bob  '), ('  Charlie  ');
SELECT LTRIM(name) AS name_no_leading FROM customers;
-- Write SELECT query using RTRIM here

Need a hint?

Use SELECT RTRIM(name) AS name_no_trailing FROM customers; to remove spaces on the right side.

4

Add a query to remove both leading and trailing spaces with TRIM

Write a SELECT query that returns the name column from customers but removes both leading and trailing spaces using the TRIM function. Name the output column name_trimmed.

SQL

CREATE TABLE customers (name VARCHAR(50));
INSERT INTO customers (name) VALUES ('  Alice'), ('Bob  '), ('  Charlie  ');
SELECT LTRIM(name) AS name_no_leading FROM customers;
SELECT RTRIM(name) AS name_no_trailing FROM customers;
-- Write SELECT query using TRIM here

Need a hint?

Use SELECT TRIM(name) AS name_trimmed FROM customers; to remove spaces on both sides.