UPPER, LOWER, LENGTH and TRIM
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Hand-typed data
Field agents report wild Codémon sightings from their phones — and type however they like. Welcome to messy data:
The same reporter appears as ' alex', 'alex' and 'Alex'. To a database, these are three different values. Look what that does to a count:
SELECT COUNT(DISTINCT reporter) AS reporters
FROM sightings;11 "different" reporters for 4 real people. String functions exist to fix exactly this kind of problem.
The official list
The codemon table is the clean reference (name, type, level). At the end of the lesson you will match the messy reports against it.
Exercise 2 — UPPER and LOWER
UPPER() and LOWER() change the case of a text. They do not modify the table — they transform the value in the result:
SELECT codemon, UPPER(codemon) AS shouted, LOWER(codemon) AS quiet
FROM sightings
LIMIT 5;Their real job is comparison. WHERE codemon = 'Aquachu' finds one row; lower-casing the column finds both spellings:
SELECT sighting_id, codemon
FROM sightings
WHERE LOWER(codemon) = 'aquachu';Compare in one case
Put LOWER() (or UPPER()) on the column and write the literal in that same case. In SQLite, LIKE already ignores case for ASCII letters, but = does not.
Exercise 3 — LENGTH and TRIM
LENGTH() counts the characters — and reveals invisible spaces. TRIM() removes the spaces at both ends:
SELECT reporter, TRIM(reporter) AS clean,
LENGTH(reporter) AS before_trim, LENGTH(TRIM(reporter)) AS after_trim
FROM sightings
WHERE reporter LIKE ' %' OR reporter LIKE '% ';Three reporters were typed with stray spaces: ' alex' is 6 characters long, 'alex' only 4. LTRIM() and RTRIM() clean one side only.
TRIM only touches the ends
TRIM('Azure City') keeps the inner space. To strip other characters, pass them as a second argument: TRIM('--AZ--', '-') gives 'AZ'.
Exercise 4 — Case and space
' alex', 'alex' and 'Alex' must be treated as the same person. Which expression makes all three equal?
LOWER(TRIM(reporter))TRIM(reporter)LOWER(reporter)LENGTH(reporter)Hint : Two problems, two functions.
Exercise 5 — Normalize, then count
Chain the two functions: LOWER(TRIM(…)) gives one canonical form for every spelling. The count comes out right:
SELECT COUNT(DISTINCT LOWER(TRIM(reporter))) AS reporters
FROM sightings;And GROUP BY the normalized value to get the real tally per person:
SELECT LOWER(TRIM(reporter)) AS reporter, COUNT(*) AS reports
FROM sightings
GROUP BY LOWER(TRIM(reporter))
ORDER BY reports DESC, reporter;Store clean, query clean
Normalizing at query time works, but it is slow on big tables and easy to forget. The real fix is to clean values once, when they enter the database — a later lesson on UPDATE shows how.
Exercise 6 — Distinct Codémons
Complete the query so that it lists each reported Codémon name once, in lowercase and without stray spaces, in alphabetical order.
Hint : Remove the spaces inside the LOWER, then sort by the cleaned alias.
Exercise 7 — What comes out?
Match each expression with its result.
UPPER('Azure City')LENGTH(' alex')TRIM(' alex')LENGTH(TRIM(' alex'))'AZURE CITY'6'alex'4Exercise 8 — Eleven reporters?
Only 4 people file reports, yet SELECT COUNT(DISTINCT reporter) FROM sightings returns 11. Why?
Hint : To SQL, a capital letter or a space is a different character.
Exercise 9 — Seen twice
Professor Pi wants to know which Codémons were reported more than once — whatever the spelling.
Return codemon (lowercase, trimmed) and times_seen, only for Codémons seen at least twice. Sort by codemon.
-- Write your query below