TRIM, LTRIM, RTRIM in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of TRIM removing spaces

What is the output of the following SQL query?

SELECT TRIM(' hello world ') AS trimmed_text;

SQL

SELECT TRIM('  hello world  ') AS trimmed_text;

A ' hello world '

B ' hello world'

C 'hello world '

D 'hello world'

💡 Hint

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

The TRIM function removes all leading and trailing spaces from the string, so the output is 'hello world' without spaces on either side.

❓ query_result

intermediate

Output of LTRIM removing leading spaces

What is the output of this SQL query?

SELECT LTRIM(' data science ') AS left_trimmed;

SQL

SELECT LTRIM('  data science  ') AS left_trimmed;

A 'data science '

B ' data science'

C 'data science'

D ' data science '

💡 Hint

LTRIM removes spaces only from the start of the string.

LTRIM removes spaces from the left side only, so trailing spaces remain intact.

❓ query_result

advanced

Output of RTRIM removing trailing spaces

What does this SQL query return?

SELECT RTRIM(' database ') AS right_trimmed;

SQL

SELECT RTRIM('  database  ') AS right_trimmed;

A 'database '

B ' database'

C 'database'

D ' database '

💡 Hint

RTRIM removes spaces only from the end of the string.

RTRIM removes trailing spaces, so leading spaces remain but trailing spaces are removed.

🧠 Conceptual

advanced

Difference between TRIM, LTRIM, and RTRIM

Which statement correctly describes the difference between TRIM, LTRIM, and RTRIM functions in SQL?

A TRIM removes spaces from both ends; LTRIM removes spaces only from the left; RTRIM removes spaces only from the right.

B TRIM removes spaces only from the right; LTRIM removes spaces only from the left; RTRIM removes spaces from both ends.

C TRIM removes spaces only from the left; LTRIM removes spaces from both ends; RTRIM removes spaces only from the right.

D TRIM removes spaces only from the middle; LTRIM removes spaces only from the right; RTRIM removes spaces only from the left.

💡 Hint

Think about which side each function trims spaces from.

TRIM removes spaces from both the start and end of a string. LTRIM removes spaces only from the start (left side). RTRIM removes spaces only from the end (right side).

📝 Syntax

expert

Identify the syntax error in TRIM usage

Which option will cause a syntax error when trying to remove leading and trailing spaces from the string ' example ' in standard SQL?

A SELECT TRIM(LEADING ' ' FROM ' example ') AS result;

B SELECT TRIM(' example ') AS result;

C SELECT TRIM(BOTH FROM ' example ') AS result;

D SELECT TRIM(BOTH ' ' FROM ' example ') AS result;

💡 Hint

Check the correct syntax for specifying which characters to trim in TRIM function.

Option C is missing the characters to trim after BOTH keyword, causing a syntax error. The correct syntax requires specifying the characters to trim, e.g., TRIM(BOTH ' ' FROM string).

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