Clean and reshape text
Part 1 of 2
Clean text without changing the evidence
One addresses row is one recorded location; one scans row is one tracking event. A scalar function transforms a value for each row. An aggregate summarizes rows. LOWER(name) does not mean the same kind of operation as COUNT(name), even though both use parentheses.
TRIM removes ordinary spaces from the ends; LOWER and UPPER standardize letter case. Nest functions from the inside out: LOWER(TRIM(type)) trims before changing case. These SELECT expressions change the report, not the stored value. Keep the original beside the cleaned output so another analyst can check what changed.
Case cleanup is not identity matching: two addresses with the same normalized label can still be different locations. SQLite’s built-in LOWER/UPPER cover ASCII letters; do not assume this is complete international name normalization.
Worked example
SELECT id, type, LOWER(TRIM(type)) AS clean_type FROM addresses ORDER BY id LIMIT 5;
The first five address records keep their identifiers and raw types. A different clean_type reveals a display transformation you can inspect.
Re-run SELECT id, type FROM addresses to inspect the stored values. Cleaning for a report and editing source evidence are separate decisions.
Part 2 of 2
Slice, replace, and build a readable label
SUBSTR starts at position 1. SUBSTR(name, 1, 2) keeps the first two characters; a negative start counts from the end. LENGTH counts characters, including spaces. Test a short literal before applying a position rule to an entire table.
REPLACE substitutes matching text wherever it occurs and is case-sensitive. It does not know which words are street suffixes. Compare the original with the shortened text to catch unintended changes.
The || operator concatenates text. Put separators such as spaces or dashes inside single-quoted literals. The + operator performs numeric addition. A plausible-looking numeric result can therefore be a logic error in a label query.
Worked example
SELECT id, address, REPLACE(address, 'Road', 'Rd') AS compact_address FROM addresses WHERE address LIKE '%Road%' ORDER BY id;
The WHERE clause selects address strings containing Road. REPLACE changes matching spelling, so examine the two columns rather than assuming every match was a suffix.
For the exercise, calculate the inside expression first. Short codes are display labels; id remains the primary key.