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

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

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

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

Hint

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

Practice

1. What does the SQL function LTRIM do to a string?

easy

A. Removes all spaces inside the string

B. Removes spaces only from the end (right side) of the string

C. Removes spaces from both the start and end of the string

D. Removes spaces only from the start (left side) of the string

Solution

1. Step 1: Understand LTRIM function purpose

   LTRIM removes spaces only from the left side (start) of a string.

2. Step 2: Compare with other trim functions

   Unlike RTRIM (right side) or TRIM (both sides), LTRIM targets only the start.

3. Final Answer:

   Removes spaces only from the start (left side) of the string -> Option D

4. Quick Check:

   LTRIM = left side trim [OK]

Hint: LTRIM trims left side spaces only [OK]

Common Mistakes:

• Confusing LTRIM with RTRIM
• Thinking LTRIM removes spaces from both sides
• Assuming LTRIM removes spaces inside the string

2. Which of the following is the correct syntax to remove trailing spaces from a column named name in SQL?

easy

A. SELECT TRIM(name) FROM table;

B. SELECT LTRIM(name) FROM table;

C. SELECT RTRIM(name) FROM table;

D. SELECT REMOVE_TRAIL(name) FROM table;

Solution

1. Step 1: Identify function to remove trailing spaces

   RTRIM removes spaces from the right (end) side of a string.

2. Step 2: Check syntax correctness

   SELECT RTRIM(name) FROM table; is valid SQL syntax for this purpose.

3. Final Answer:

   SELECT RTRIM(name) FROM table; -> Option C

4. Quick Check:

   RTRIM trims right side spaces [OK]

Hint: RTRIM removes trailing (right) spaces [OK]

Common Mistakes:

• Using LTRIM instead of RTRIM for trailing spaces
• Using TRIM which removes both sides, not just trailing
• Using non-existent function REMOVE_TRAIL

3. Given the string value ' Hello World ', what will the result of SELECT TRIM(' Hello World '); be?

medium

A. 'Hello World'

B. ' Hello World'

C. 'Hello World '

D. 'HelloWorld'

Solution

1. Step 1: Understand TRIM function behavior

   TRIM removes spaces from both the start and end of the string.

2. Step 2: Apply TRIM to the given string

   Spaces before and after 'Hello World' are removed, but spaces inside remain.

3. Final Answer:

   'Hello World' -> Option A

4. Quick Check:

   TRIM removes spaces both sides [OK]

Hint: TRIM removes spaces both sides, not inside [OK]

Common Mistakes:

• Expecting spaces inside the string to be removed
• Confusing TRIM with LTRIM or RTRIM
• Including quotes incorrectly in output

4. You wrote the query SELECT LTRIM(name) FROM users; but it does not remove spaces from the end of the names. What is the issue?

medium

A. The column name should be in quotes for LTRIM to work

B. LTRIM only removes spaces from the start, not the end

C. You should use TRIM to remove spaces only from the end

D. LTRIM removes spaces from both sides, so query is correct

Solution

1. Step 1: Understand LTRIM function limitation

   LTRIM removes spaces only from the left (start) side of a string.

2. Step 2: Identify why trailing spaces remain

   Spaces at the end (right side) are not removed by LTRIM, so they remain.

3. Final Answer:

   LTRIM only removes spaces from the start, not the end -> Option B

4. Quick Check:

   LTRIM trims left side only [OK]

Hint: LTRIM does not remove trailing spaces [OK]

Common Mistakes:

• Assuming LTRIM removes spaces from both sides
• Using quotes incorrectly around column names
• Confusing TRIM and LTRIM functions

5. You have a table products with a column code that sometimes contains spaces at both ends. You want to update the table to remove all leading and trailing spaces from code. Which SQL statement correctly does this?

hard

A. UPDATE products SET code = TRIM(code);

B. UPDATE products SET code = LTRIM(code);

C. UPDATE products SET code = RTRIM(code);

D. UPDATE products SET code = REMOVE_SPACES(code);

Solution

1. Step 1: Understand how to remove spaces from both ends

   TRIM removes spaces from both the start and end of a string in one call.

2. Step 2: Evaluate other options

   LTRIM removes only leading spaces, RTRIM only trailing spaces. Neither alone removes both sides.

3. Step 3: Check for invalid functions

   REMOVE_SPACES is not a valid SQL function.

4. Final Answer:

   UPDATE products SET code = TRIM(code); -> Option A

5. Quick Check:

   TRIM removes both sides spaces [OK]

Hint: Use TRIM to clean both sides in one step [OK]

Common Mistakes:

• Using LTRIM which removes only leading spaces
• Using RTRIM which removes only trailing spaces
• Using invalid function REMOVE_SPACES