10.1 How to Clean Text with trim(), upper() and lower()

By Ravindra Bagale Last updated: 31 October 2026

Why. Names typed by hand often have extra spaces and mixed capital letters. Then one name can look like two different names in a report.

What. trim() with one argument removes plain space characters from both ends of a text. It does not remove spaces in the middle, and it does not remove tabs or line breaks. upper() turns letters into capitals and lower() turns them into small letters. In SQLite, upper() and lower() change only plain English letters A to Z by default, so a letter like é stays as it is.

How. Put square brackets around the name so you can see the spaces. Then compare the raw name with trim(), upper() and lower().

Steps

Open swiftship_logistics.db in DB Browser for SQLite and go to the Execute SQL tab.
Run the first example and compare the shown and trimmed columns.
Check the two length columns: the number drops only when spaces at the ends are removed.
Run the second example and see that é is not changed by upper().

Examples

Example 1 · dataset: swiftship_logistics

SELECT agent_id,
       '[' || raw_name || ']'       AS shown,
       '[' || trim(raw_name) || ']' AS trimmed,
       length(raw_name)             AS len_raw,
       length(trim(raw_name))       AS len_trimmed
FROM agents
ORDER BY agent_id;

Output

agent_id	shown	trimmed	len_raw	len_trimmed
1	[ asha patil ]	[asha patil]	13	10
2	[RAHUL SHARMA]	[RAHUL SHARMA]	12	12
3	[Fatima Khan]	[Fatima Khan]	12	12
4	[oliver brown]	[oliver brown]	12	12
5	[élodie martin]	[élodie martin]	13	13
6	NULL	NULL	NULL	NULL

Meaning. Agent 1 had two spaces at the start and one at the end, so the length drops from 13 to 10. Fatima still has two spaces in the middle, because trim() only cleans the ends. Agent 6 has no name at all, so every column is NULL.

Example 2 · dataset: swiftship_logistics

SELECT raw_name,
       upper(raw_name) AS upper_name,
       lower(raw_name) AS lower_name
FROM agents
WHERE agent_id IN (2, 5);

Output

raw_name	upper_name	lower_name
RAHUL SHARMA	RAHUL SHARMA	rahul sharma
élodie martin	éLODIE MARTIN	élodie martin

Meaning. RAHUL SHARMA becomes rahul sharma with lower(). For élodie martin, upper() changes the English letters but leaves é as a small letter.

Ravindra Bagale's Tip

Compare names with lower(trim(name)) on both sides. It removes the most common spacing and capital-letter differences. A match only shows a possible duplicate, so check the IDs before you merge two records.