UPPER, LOWER, LENGTH and TRIM

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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?

Options
• 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.

Items to match
• UPPER('Azure City')
• LENGTH(' alex')
• TRIM(' alex')
• LENGTH(TRIM(' alex'))
Definitions
• 'AZURE CITY'
• 6
• 'alex'
• 4

Exercise 8 — Eleven reporters?

Only 4 people file reports, yet SELECT COUNT(DISTINCT reporter) FROM sightings returns 11. Why?

Options
• 'Alex', 'alex' and ' alex' are different text values; normalize with LOWER(TRIM()) first
• COUNT(DISTINCT) also counts the NULL values
• DISTINCT does nothing on TEXT columns
• COUNT only works on numeric columns

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.

Starter code
-- Write your query below
Back to the course