Brightlane's data engineering team is testing how TRIM handles its characters argument. The string 'ababHELLOabab' carries leading and trailing runs of 'a' and 'b' characters around an inner 'HELLO' segment.
Write a query to return the string with every leading and trailing 'a' or 'b' character removed.
Output:
- A single row with one column,
trimmed, containing the result.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
TRIM(
'ab'
FROM
'ababHELLOabab'
) AS trimmedThe shape
The characters argument to TRIM is a set, not a substring. TRIM('ab' FROM 'ababHELLOabab') reads the second argument as the set {a, b} and strips any leading or trailing character that belongs to that set, one at a time, until it hits a character that does not. The four-character runs of 'a' and 'b' on each end are removed independently and the inner 'HELLO' is left untouched.
Clause by clause
SELECT TRIM('ab' FROM 'ababHELLOabab') AS trimmedruns the set-basedTRIMagainst the literal and labels the resulttrimmed. From the left, the function removes the first'a', then the'b', then the'a', then the'b', and stops at the'H'because'H'is not in the set{a, b}. From the right, it removes the trailing'b','a','b','a'and stops at the'O'for the same reason. The middle'HELLO'is the part neither pass touches.- There is no
FROMbecause the value being trimmed is the literal in theSELECT. The function operates on one input and returns one output.
Why this and not a substring match
A reader expecting TRIM to behave like a literal prefix-and-suffix match would predict that TRIM('ab' FROM 'ababHELLOabab') strips two occurrences of the substring 'ab' from the left and two from the right, also producing 'HELLO' on this specific input. The two interpretations agree here by accident. They disagree the moment the input changes. TRIM('ab' FROM 'baHELLOba') produces 'HELLO' under set semantics (both b and a are in the set, so both get stripped) and produces 'baHELLOba' under substring semantics (the literal 'ab' does not appear at either end). The actual PostgreSQL behavior is set semantics.
The trap
The character order in the set argument does not matter, and the second argument is not a prefix to look for. TRIM('ab' FROM s) and TRIM('ba' FROM s) are identical: both strip any leading or trailing 'a' or 'b' until they hit a character outside the set. A reader who writes TRIM('xy' FROM 'xyhelloyx') expecting it to strip the literal 'xy' from the front and back will be surprised when 'helloyx' is not what comes out either, because the 'x' at the very end is also in the set and gets removed too. The rule is the set, not the spelling: whenever TRIM is called with a multi-character second argument, every character in that argument is treated as a separately removable character on each end.
You practiced TRIM('chars' FROM ...) — the characters argument is a set, not a substring, so every leading or trailing character that appears in the set is removed independently.
