TRIM, LTRIM, RTRIM variations in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of TRIM with both leading and trailing characters

What is the output of the following SQL query?

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

PostgreSQL

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

A 'hello'

B 'helloxx'

C 'xxhelloxx'

D 'xxhello'

💡 Hint

TRIM removes specified characters from both ends of the string.

TRIM(BOTH 'x' FROM 'xxhelloxx') removes all 'x' characters from the start and end, leaving 'hello'.

❓ query_result

intermediate

Result of LTRIM removing multiple characters

What is the result of this query?

SELECT LTRIM('abc123abc', 'abc');

PostgreSQL

SELECT LTRIM('abc123abc', 'abc');

A '123abc'

B 'bc123abc'

C '123'

D 'abc123abc'

💡 Hint

LTRIM removes all characters in the set from the start until a character not in the set is found.

LTRIM removes all leading 'a', 'b', and 'c' characters from the start, stopping at '1'. So the result is '123abc'.

📝 Syntax

advanced

Identify the syntax error in RTRIM usage

Which option contains a syntax error when trying to remove trailing spaces using RTRIM in PostgreSQL?

A SELECT RTRIM(' hello ', ' ');

B SELECT RTRIM(' hello ', '');

C SELECT RTRIM(' hello ');

D SELECT RTRIM(' hello ', ' ');

💡 Hint

The characters to remove cannot be an empty string.

RTRIM with an empty string as the second argument is invalid syntax in PostgreSQL and causes an error.

❓ query_result

advanced

Effect of TRIM with no characters specified

What is the output of this query?

SELECT TRIM(' hello ');

Note: No characters specified after TRIM.

PostgreSQL

SELECT TRIM('  hello  ');

A 'hello '

B ' hello '

C 'hello'

D ' hello'

💡 Hint

When no characters are specified, TRIM removes spaces from both ends.

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

🧠 Conceptual

expert

Understanding TRIM variations with multiple characters

Given the string 'xyxzyhellozzyx', which TRIM function call will remove all leading and trailing 'x', 'y', and 'z' characters?

Choose the correct query.

A SELECT TRIM(TRAILING 'xyz' FROM 'xyxzyhellozzyx');

B SELECT TRIM(BOTH 'xzy' FROM 'xyxzyhellozzyx');

C SELECT TRIM(LEADING 'xyz' FROM 'xyxzyhellozzyx');

D SELECT TRIM(BOTH 'xyz' FROM 'xyxzyhellozzyx');

💡 Hint

The order of characters in the trim set does not matter; BOTH removes from both ends.

Option D removes all leading and trailing 'x', 'y', and 'z' characters from both ends, leaving 'hello'.