SQL TRIM: Distinguish Whitespace From a Character Set

The practice task

Clean fictional labels and compare default space trimming with trimming the characters x and y. Preserve the quoted representation in the result so leading and trailing spaces remain visible.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE labels(value TEXT);
INSERT INTO labels VALUES ('  room  '),('xyroomyx'),('room xy middle');

SELECT QUOTE(value) AS original, QUOTE(TRIM(value)) AS spaces_removed,
 QUOTE(TRIM(value,'xy')) AS xy_removed FROM labels ORDER BY value;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

Default TRIM removes the outer spaces from the first label. Supplying xy removes the outer x and y characters from the last label even though their order differs at the two ends. The middle label keeps its internal x, y and spaces.

The second argument is not a request to replace the substring xy everywhere. Internal text stays in place. If a data-cleaning rule requires removing one exact prefix, first test that prefix and then extract the remainder rather than using a character-set trim by accident.

This fixture is deliberately ASCII. Different whitespace characters, such as tabs or non-breaking spaces, need explicit handling based on the source data. Inspect the actual characters before assuming that every visually blank edge will be removed by the default function.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Check whether the function expects a character set or a complete substring.
• Test internal text as well as both ends.
• Keep an original value available when evaluating a cleaning rule.