skip to content

In SQL, what does TRIM(LEADING '0' FROM code) return for '00120' and '000', and what does a bare TRIM(code) remove?

level: juniorimportance: should knowfreq 42%

answer

  1. a side, a character, a string
  2. two defaults when you omit them
  3. the all-zero code
  4. space is not all whitespace
  5. several trim characters, several meanings

basics

~20 s

TRIM(LEADING '0' FROM code) turns '00120' into '120' and '000' into an empty string, or NULL on Oracle. A bare TRIM(code) removes only space characters from both ends, leaving tabs, newlines and interior spaces in place.

solid answer

~50 s

`TRIM` takes a side, a character and a string. The side is `LEADING`, `TRAILING` or `BOTH`, and `BOTH` plus a space are the defaults, so `TRIM(code)` means `TRIM(BOTH ' ' FROM code)`. `TRIM(LEADING '0' FROM '00120')` gives `'120'`, and it keeps removing until a different character appears, so `'000'` becomes an empty string, or NULL on Oracle, where an empty string is NULL. That is the import trap: a legitimate all-zero code vanishes. The other trap is that the default removes only the space character, so a value ending in a tab, a newline or a non-breaking space still fails an equality test after `TRIM`, and interior spaces stay as well. A multi-character argument is not portable either: PostgreSQL treats it as a set of characters, MySQL as one whole string, and Oracle rejects it. In portable code I pass one character.

go deeper

for a junior

Know the three side keywords, the two defaults, and that only the ends are touched. Be ready to say what happens to '000' and to a value ending in a tab.

for a middle

Explain why a multi-character argument is unsafe across engines, using the set-versus-whole-string difference, and how LPAD truncates a value that is already longer than its target width.

for a senior

When values that look equal do not compare equal, suspect invisible characters first, and show how you would prove it with length checks and byte inspection instead of reading sample rows.

for a principal

Decide what canonical form a code has and write it down, including all-zero codes and width. Without one agreed form, each pipeline trims differently and joins between systems slowly lose rows.

## The full form of TRIM `TRIM` removes characters from the ends of a string, never from the middle. Its full form has three parts: ```sql TRIM(LEADING '0' FROM code) -- start of the string only TRIM(TRAILING ' ' FROM code) -- end of the string only TRIM(BOTH '-' FROM code) -- both ends TRIM(code) -- BOTH and a space are the defaults ``` - **`LEADING`** removes from the start, **`TRAILING`** from the end, **`BOTH`** from each. With no side keyword, `BOTH` applies. - The **trim character** defaults to a single space. - A **NULL** input returns NULL, so `TRIM` will not repair a missing value. Removal is repeated: `TRIM` keeps going until it meets a character it was not asked to remove, so one call strips any number of leading zeros. ## Leading zeros and padded codes Imports often carry product or branch codes padded with zeros, and a system downstream may hold the same codes unpadded. `TRIM` handles the normal case: | Input | `TRIM(LEADING '0' FROM code)` | |---|---| | `'00120'` | `'120'` | | `'120'` | `'120'` | | `'00100'` | `'100'` (trailing zeros stay) | | `'000'` | Empty string (NULL on Oracle) | | NULL | NULL | The fourth row is the one to raise in an interview. If `'000'` is a real code, it is gone after cleaning, and on most engines it has become an empty string that passes a `code IS NOT NULL` check. Oracle returns NULL because it treats an empty string as NULL. If all-zero codes can exist, handle them explicitly: ```sql SELECT CASE WHEN TRIM(LEADING '0' FROM code) = '' THEN '0' ELSE TRIM(LEADING '0' FROM code) END AS clean_code FROM customer_import; ``` That guard does not work on Oracle, where `= ''` is never true, so there the test has to be `IS NULL`. ## What the default does not remove The most common `TRIM` bug in real data is believing it removes whitespace. It removes the **space character**. These all survive a bare `TRIM`: - Tabs and newlines at either end, often left behind by a spreadsheet or a CSV export. - Non-breaking spaces, common in text pasted from a web page. They look identical to a space on screen. - Every space between words, so `'Ann Lee'` keeps its double space. The symptom is an equality test that keeps failing on values that look identical. The way to spot it is to compare `CHAR_LENGTH(code)` with the length you expect, or to inspect the raw bytes of a failing row. ## Multi-character trim arguments The argument looks like a string, but engines disagree about what it means. This is a real source of wrong results when a query moves between engines: | Engine | Meaning of a multi-character argument | `TRIM(TRAILING 'xyz' FROM 'barxxyz')` | |---|---|---| | PostgreSQL | A set: remove any of these characters | `'bar'` | | MySQL | One whole string, removed repeatedly | `'barx'` | | Oracle | Not allowed: one character only | Error | SQL Server also treats the argument as a set of characters, but accepted the `LEADING`, `TRAILING` and `BOTH` keywords only in its 2022 release, so check the version before relying on them there. The safe portable habit is to pass one character. ## Padding back Cleaning often ends by restoring a fixed width. `LPAD(string, length, fill)` prepends the fill characters until the value reaches the length, so `LPAD('120', 6, '0')` is `'000120'`. Two things to remember: 1. `LPAD` **truncates** a value that is already too long: `LPAD('1234567', 6, '0')` is `'123456'`, cut on the right, with no error. Check the length first, or an over-long code silently becomes a different code. 2. `LPAD` is widely available but not universal. SQL Server has traditionally built the same width from `RIGHT` and `REPLICATE`, so a padding expression rarely moves between engines untouched. ## A habit worth having Clean one way, then verify one way. After a cleaning step, count three things: - How many values changed. - How many became empty. - How many now have an unexpected length. A small count of each is a far better signal than reading sample rows, because the rows that break a report are rarely the ones in the first page of output.

  • What does TRIM(TRAILING 'xyz' FROM 'barxxyz') return on PostgreSQL and on MySQL?
    PostgreSQL returns 'bar', because the argument is a set of characters, so it strips any trailing x, y or z. MySQL returns 'barx', because it removes the whole string 'xyz' from the end, which matches once. Oracle rejects a multi-character argument, so portable code passes a single character.
  • How do you restore a six-character code after cleaning, and what does LPAD do to a value that is too long?
    LPAD(code, 6, '0') prepends zeros up to length 6, so '120' becomes '000120'. A value already longer is cut on the right, so '1234567' becomes '123456' with no error, which silently creates a different code. Check CHAR_LENGTH before padding. LPAD is widely available, but SQL Server has traditionally used RIGHT with REPLICATE instead.
  • How do you strip tabs and newlines as well as spaces from the ends of a value?
    Where the argument is a set, list them; in PostgreSQL that is trim(BOTH E' \t\r\n' FROM name). Elsewhere, use REPLACE to turn those characters into spaces, then TRIM, accepting that interior tabs change too. A non-breaking space needs the same explicit handling, since no default removes it.

  • TRIM removes all whitespace, including tabs and newlines, from both ends of a value.
  • TRIM removes spaces everywhere in the string, including the ones between words.
  • TRIM(LEADING '0' FROM '000') leaves one zero behind, because the result must keep a digit.
  • A multi-character TRIM argument means the same thing on every engine.
  • With no side keyword, TRIM only removes characters from the right-hand end.
  • LPAD raises an error when the value is already longer than the target length.