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

Using TRIM, LTRIM, and RTRIM Functions in MySQL

📖 Scenario: You work in a customer service team. You have a database table that stores customer names, but some names have extra spaces at the start or end. These spaces can cause problems when searching or sorting names.

🎯 Goal: You will create a table with customer names that include extra spaces. Then, you will use MySQL's TRIM, LTRIM, and RTRIM functions to clean these names by removing unwanted spaces.

📋 What You'll Learn

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

Add a variable to hold a sample name with spaces

Write a query using LTRIM to remove spaces from the start of the name

Write a query using RTRIM to remove spaces from the end of the name

Write a query using TRIM to remove spaces from both ends of the name

💡 Why This Matters

🌍 Real World

Cleaning user input or imported data that may have unwanted spaces is common in databases to ensure accurate searching and reporting.

💼 Career

Database administrators and developers often need to clean and normalize text data to maintain data quality and improve query results.

1

Create the customers table with names having extra spaces

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

MySQL

-- Create the customers table and insert names with spaces
-- Your code here

Need a hint?

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

2

Set a variable with a sample name having spaces

Create a variable called @sample_name and set it to the string ' David ' including the spaces.

MySQL

CREATE TABLE customers (name VARCHAR(50));
INSERT INTO customers (name) VALUES ('  Alice'), ('Bob  '), ('  Charlie  ');
-- Set the variable @sample_name with '  David  '
-- Your code here

Need a hint?

Use SET @sample_name = ' David '; to create the variable with spaces.

3

Use LTRIM and RTRIM to remove spaces from one side

Write two SELECT queries: one that uses LTRIM(@sample_name) to remove spaces from the start, and another that uses RTRIM(@sample_name) to remove spaces from the end.

MySQL

CREATE TABLE customers (name VARCHAR(50));
INSERT INTO customers (name) VALUES ('  Alice'), ('Bob  '), ('  Charlie  ');
SET @sample_name = '  David  ';
-- Select LTRIM(@sample_name) and RTRIM(@sample_name)
-- Your code here

Need a hint?

Use SELECT LTRIM(@sample_name) and SELECT RTRIM(@sample_name) to see the trimmed results.

4

Use TRIM to remove spaces from both sides

Write a SELECT query that uses TRIM(@sample_name) to remove spaces from both the start and end of the string.

MySQL

CREATE TABLE customers (name VARCHAR(50));
INSERT INTO customers (name) VALUES ('  Alice'), ('Bob  '), ('  Charlie  ');
SET @sample_name = '  David  ';
SELECT LTRIM(@sample_name) AS LeftTrimmedName;
SELECT RTRIM(@sample_name) AS RightTrimmedName;
-- Select TRIM(@sample_name) to remove spaces from both sides
-- Your code here

Need a hint?

Use SELECT TRIM(@sample_name) to remove spaces from both ends.