TRIM, LTRIM, RTRIM in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the TRIM function do in SQL?

TRIM removes spaces or specified characters from both the beginning and the end of a string.

beginner

How does LTRIM differ from RTRIM?

LTRIM removes spaces or specified characters only from the start (left side) of a string, while RTRIM removes them only from the end (right side).

beginner

Write a SQL query to remove spaces from the left side of the string ' hello '.

SELECT LTRIM(' hello '); -- Result: 'hello '

beginner

Why would you use TRIM functions in a database?

To clean data by removing unwanted spaces or characters that can cause errors or mismatches in searches and comparisons.

intermediate

Can TRIM remove characters other than spaces? How?

Yes, by specifying the character to remove inside the TRIM function, for example: TRIM('x' FROM 'xxhelloxx') returns 'hello'.

What does RTRIM('hello ') return?

A 'olleh'

B ' hello'

C 'hello '

D 'hello'

RTRIM removes spaces from the right side, so trailing spaces after 'hello' are removed.

Which function removes spaces only from the left side of a string?

A TRIM

B LTRIM

C RTRIM

D REMOVE

LTRIM removes spaces from the left side only.

How do you remove the character 'x' from both ends of 'xxhelloxx'?

A TRIM('x' FROM 'xxhelloxx')

B LTRIM('x' FROM 'xxhelloxx')

C RTRIM('x' FROM 'xxhelloxx')

D REMOVE('x', 'xxhelloxx')

TRIM with 'x' removes 'x' characters from both ends.

What will TRIM(' hello ') return?

A 'hello '

B ' hello'

C 'hello'

D ' hello '

TRIM removes spaces from both ends, leaving 'hello'.

If you want to remove spaces only from the right side of a string, which function do you use?

A RTRIM

B LTRIM

C TRIM

D RIGHTTRIM

RTRIM removes spaces from the right side only.

Explain the difference between TRIM, LTRIM, and RTRIM functions in SQL.

Think about which side of the string each function cleans.

Describe a real-life situation where using TRIM functions would help clean data.

Imagine typing a name with spaces by mistake.

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