TRIM, LTRIM, RTRIM variations in PostgreSQL - Step-by-Step Execution

Concept Flow - TRIM, LTRIM, RTRIM variations

Input String

↓

Choose Trim Function

↓

TRIM

↓

Remove spaces or chars

↓

Return trimmed string

The input string goes through one of the trim functions (TRIM, LTRIM, RTRIM) which remove spaces or specified characters from either both ends, left end, or right end, then returns the cleaned string.

Execution Sample

PostgreSQL

SELECT TRIM('  hello  ') AS trimmed_full,
       LTRIM('  hello  ') AS trimmed_left,
       RTRIM('  hello  ') AS trimmed_right;

This query trims spaces from both ends, left end, and right end of the string ' hello ' using TRIM, LTRIM, and RTRIM respectively.

Execution Table

Step	Function	Input String	Operation	Output String
1	TRIM	' hello '	Remove spaces from both ends	'hello'
2	LTRIM	' hello '	Remove spaces from left end	'hello '
3	RTRIM	' hello '	Remove spaces from right end	' hello'
4	TRIM(BOTH 'x' FROM 'xxhelloxx')	'xxhelloxx'	Remove 'x' from both ends	'hello'
5	LTRIM('xxhelloxx', 'x')	'xxhelloxx'	Remove 'x' from left end	'helloxx'
6	RTRIM('xxhelloxx', 'x')	'xxhelloxx'	Remove 'x' from right end	'xxhello'
7	TRIM(BOTH FROM ' hello ')	' hello '	Default trim spaces both ends	'hello'
8	LTRIM(' hello ')	' hello '	Default trim spaces left end	'hello '
9	RTRIM(' hello ')	' hello '	Default trim spaces right end	' hello'
10	End		All trimming operations complete	N/A

💡 All trimming operations finished, strings returned with specified characters removed from ends.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6
Input String	' hello '	'hello'	'hello '	' hello'	'xxhelloxx'	'xxhelloxx'	'xxhelloxx'
Output String	N/A	'hello'	'hello '	' hello'	'hello'	'helloxx'	'xxhello'

Key Moments - 3 Insights

Why does TRIM remove spaces from both ends but LTRIM and RTRIM only remove from one side?

TRIM is designed to remove characters from both the left and right ends simultaneously, as shown in execution_table rows 1 and 4. LTRIM only removes from the left (rows 2 and 5), and RTRIM only from the right (rows 3 and 6). This is why their outputs differ.

What happens if I specify a character other than space in TRIM, LTRIM, or RTRIM?

When you specify a character, the functions remove that character from the specified ends instead of spaces. For example, in rows 4, 5, and 6, 'x' is removed from the ends instead of spaces.

If I don't specify a character, what does TRIM remove by default?

By default, TRIM, LTRIM, and RTRIM remove spaces if no character is specified, as shown in rows 7, 8, and 9.

Visual Quiz - 3 Questions

Look at the execution_table row 2. What is the output string after LTRIM(' hello ')?

A 'hello'

B ' hello'

C 'hello '

D ' hello '

💡 Hint

Check the Output String column in row 2 of the execution_table.

At which step does TRIM remove the character 'x' from both ends?

A Step 4

B Step 5

C Step 1

D Step 6

💡 Hint

Look for TRIM with 'x' character removal in the Function column.

If you use RTRIM('xxhelloxx', 'x'), what will be the output string?

A 'hello'

B 'xxhello'

C 'helloxx'

D 'xxhelloxx'

💡 Hint

Refer to execution_table row 6 Output String.

Concept Snapshot

TRIM removes characters from both ends of a string.
LTRIM removes characters from the left end only.
RTRIM removes characters from the right end only.
By default, they remove spaces unless a specific character is given.
Syntax examples:
TRIM(BOTH 'x' FROM string), LTRIM(string, 'x'), RTRIM(string, 'x')

Full Transcript

This visual execution shows how PostgreSQL's TRIM, LTRIM, and RTRIM functions work. The input string is processed by one of these functions to remove spaces or specified characters from either both ends, the left end, or the right end. The execution table traces each step with input, operation, and output. Key moments clarify common confusions about default behavior and character specification. The visual quiz tests understanding of outputs at specific steps. The snapshot summarizes syntax and behavior for quick reference.

Practice

1. What does the TRIM function do by default in PostgreSQL?

easy

A. Removes spaces only from the start of a string

B. Removes all spaces inside the string

C. Removes spaces only from the end of a string

D. Removes spaces from both the start and end of a string

Solution

1. Step 1: Understand the TRIM function behavior

   The TRIM function removes unwanted characters from a string. By default, it removes spaces.

2. Step 2: Identify which sides are affected

   By default, TRIM removes spaces from both the start (left) and end (right) of the string.

3. Final Answer:

   Removes spaces from both the start and end of a string -> Option D

4. Quick Check:

   TRIM removes spaces both sides = B [OK]

Hint: TRIM cleans both ends of spaces by default [OK]

Common Mistakes:

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

2. Which of the following is the correct syntax to remove only leading spaces from a string ' hello' in PostgreSQL?

easy

A. SELECT TRIM(' hello');

B. SELECT RTRIM(' hello');

C. SELECT LTRIM(' hello');

D. SELECT TRIM(LEADING ' hello');

Solution

1. Step 1: Identify function for leading spaces

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

2. Step 2: Check syntax correctness

   SELECT LTRIM(' hello'); correctly removes leading spaces. RTRIM removes trailing spaces, TRIM removes both sides, and SELECT TRIM(LEADING ' hello'); uses invalid syntax (missing FROM).

3. Final Answer:

   SELECT LTRIM(' hello'); -> Option C

4. Quick Check:

   LTRIM removes leading spaces = A [OK]

Hint: Use LTRIM to remove spaces only from the start [OK]

Common Mistakes:

• Using RTRIM instead of LTRIM for leading spaces
• Confusing TRIM default with LTRIM
• Using incorrect syntax like TRIM(' hello') without LEADING

3. What is the output of this query?

SELECT TRIM(BOTH 'x' FROM 'xxhelloxx');

medium

A. 'xxhello'

B. 'hello'

C. 'helloxx'

D. 'xxhelloxx'

Solution

1. Step 1: Understand TRIM with BOTH and character

   TRIM(BOTH 'x' FROM string) removes all 'x' characters from both start and end of the string.

2. Step 2: Apply trimming to 'xxhelloxx'

   Removing 'x' from start removes 'xx', from end removes 'xx', leaving 'hello'.

3. Final Answer:

   'hello' -> Option B

4. Quick Check:

   TRIM BOTH 'x' removes 'xx' both ends = A [OK]

Hint: TRIM(BOTH 'char' FROM str) removes char from both ends [OK]

Common Mistakes:

• Removing characters only from one side
• Assuming TRIM removes characters inside the string
• Confusing default TRIM with TRIM(BOTH 'char' FROM ...)

4. Identify the error in this query:

SELECT LTRIM('hello  ', ' ');

medium

A. The query is correct and will remove leading spaces

B. LTRIM removes leading spaces, so it should be RTRIM

C. The second argument should be a string of characters to remove, but space is valid

D. LTRIM cannot take a second argument

Solution

1. Step 1: Check LTRIM function signature

   LTRIM(string, characters) removes any characters in the second argument from the start of the string.

2. Step 2: Analyze the query behavior

   The query tries to remove spaces from the start of 'hello '. Since 'hello ' has no leading spaces, nothing is removed. The second argument ' ' is valid.

3. Step 3: Identify the error

   There is no syntax error, and the query is valid. It removes leading spaces if any exist. To remove trailing spaces, RTRIM should be used, but this query is correct for leading spaces.

4. Final Answer:

   The query is correct and will remove leading spaces -> Option A

5. Quick Check:

   LTRIM second arg valid, removes leading spaces if present = D [OK]

Hint: LTRIM removes leading chars; trailing spaces need RTRIM [OK]

Common Mistakes:

• Thinking LTRIM removes trailing spaces
• Believing second argument is invalid
• Expecting spaces removed from end with LTRIM

5. Given a table users with a column username containing values like ' alice ', 'bob ', and ' carol', which query correctly removes all leading and trailing spaces from usernames?

hard

A. SELECT TRIM(username) FROM users;

B. SELECT LTRIM(username) FROM users;

C. SELECT RTRIM(username) FROM users;

D. SELECT TRIM(LEADING FROM username) FROM users;

Solution

1. Step 1: Understand the goal

   We want to remove spaces from both the start and end of the username strings.

2. Step 2: Check each function's behavior

   TRIM removes spaces from both ends. LTRIM removes only leading spaces. RTRIM removes only trailing spaces. TRIM(LEADING FROM ...) removes only leading spaces.

3. Final Answer:

   SELECT TRIM(username) FROM users; -> Option A

4. Quick Check:

   TRIM removes spaces both ends = D [OK]

Hint: Use TRIM to clean spaces from both ends in one step [OK]

Common Mistakes:

• Using LTRIM or RTRIM alone to remove both sides
• Using TRIM(LEADING FROM ...) which removes only start spaces
• Not trimming spaces at all