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

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

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

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

Hint

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

Practice

1. What does the TRIM function do in MySQL?

easy

A. Removes spaces or specified characters from both ends of a string

B. Removes spaces only from the start of a string

C. Removes spaces only from the end of a string

D. Replaces spaces inside the string with underscores

Solution

1. Step 1: Understand the purpose of TRIM

   The TRIM function is designed to remove unwanted spaces or characters from the start and end of a string.

2. Step 2: Compare with LTRIM and RTRIM

   LTRIM removes only from the left, RTRIM only from the right, but TRIM does both ends.

3. Final Answer:

   Removes spaces or specified characters from both ends of a string -> Option A

4. Quick Check:

   TRIM = both ends removal [OK]

Hint: TRIM cleans both sides of spaces or chars [OK]

Common Mistakes:

• Confusing TRIM with LTRIM or RTRIM
• Thinking TRIM removes spaces inside the string
• Assuming TRIM only removes spaces from one side

2. Which of the following is the correct syntax to remove spaces from the left side of a string ' hello' in MySQL?

easy

A. RTRIM(' hello')

B. LTRIM(' hello')

C. TRIM(' hello')

D. REMOVE_LEFT(' hello')

Solution

1. Step 1: Identify the function for left-side trimming

   LTRIM is the MySQL function that removes spaces from the start (left side) of a string.

2. Step 2: Check other options

   RTRIM removes spaces from the right side, TRIM removes from both ends, and REMOVE_LEFT is not a valid MySQL function.

3. Final Answer:

   LTRIM(' hello') -> Option B

4. Quick Check:

   LTRIM = left side spaces removed [OK]

Hint: Use LTRIM() to remove left spaces [OK]

Common Mistakes:

• Using RTRIM instead of LTRIM
• Using TRIM when only left spaces needed
• Using non-existent functions like REMOVE_LEFT

3. What is the output of this query?
SELECT CONCAT('[', TRIM(' abc '), ']') AS result;

medium

A. [abc]

B. [ abc ]

C. [abc ]

D. [ abc]

Solution

1. Step 1: Understand TRIM effect on the string

   TRIM removes spaces from both the start and end of ' abc ', resulting in 'abc'.

2. Step 2: Apply CONCAT with brackets

   Concatenating '[' + 'abc' + ']' results in '[abc]'.

3. Final Answer:

   [abc] -> Option A

4. Quick Check:

   TRIM removes spaces both ends = '[abc]' [OK]

Hint: TRIM removes spaces both ends, so output is '[abc]' [OK]

Common Mistakes:

• Thinking TRIM removes spaces only from one side
• Ignoring spaces inside the string
• Confusing CONCAT output formatting

4. Identify the error in this query:
SELECT LTRIM(' test') FROM dual WHERE RTRIM('test ') = 'test';

medium

A. No error, query runs correctly

B. RTRIM cannot be used in WHERE clause

C. LTRIM and RTRIM functions are misspelled

D. Using FROM dual is invalid in MySQL

Solution

1. Step 1: Check the use of FROM dual in MySQL

   MySQL does not require or support the FROM dual syntax; it is used in Oracle.

2. Step 2: Verify function usage and WHERE clause

   LTRIM and RTRIM are valid functions and can be used in WHERE clauses in MySQL.

3. Final Answer:

   Using FROM dual is invalid in MySQL -> Option D

4. Quick Check:

   FROM dual is Oracle-specific, not MySQL [OK]

Hint: MySQL does not use FROM dual [OK]

Common Mistakes:

• Assuming FROM dual works in MySQL
• Thinking RTRIM can't be in WHERE clause
• Misspelling function names

5. You have a table users with a column username that may have extra spaces at the start or end. Which query correctly updates the table to remove all leading and trailing spaces from username?

hard

A. UPDATE users SET username = LTRIM(username);

B. UPDATE users SET username = RTRIM(username);

C. UPDATE users SET username = TRIM(username);

D. UPDATE users SET username = REPLACE(username, ' ', '');

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: Check LTRIM and RTRIM options

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

3. Step 3: Check REPLACE option

   REPLACE removes all spaces inside the string, not just at ends, which is incorrect here.

4. Final Answer:

   UPDATE users SET username = TRIM(username); -> Option C

5. Quick Check:

   TRIM cleans both ends simply [OK]

Hint: Use TRIM() to clean both ends in one step [OK]

Common Mistakes:

• Using LTRIM which only removes leading spaces
• Using RTRIM which only removes trailing spaces
• Using REPLACE which removes all spaces