SUBSTR, INSTR and REPLACE

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

β–Ά Do the exercises

Exercise 1 β€” SUBSTR: a piece of text

ref_code packs three pieces of information: region, year, number β€” 'AZ-2023-0017'. SUBSTR(text, start, length) cuts a piece out. Positions start at 1:

SELECT ref_code, SUBSTR(ref_code, 1, 2) AS region, SUBSTR(ref_code, 4, 4) AS year, SUBSTR(ref_code, 9) AS number FROM sightings LIMIT 4;

Without a length, SUBSTR goes to the end of the text. Once extracted, a piece is a value like any other β€” you can group by it:

SELECT SUBSTR(ref_code, 1, 2) AS region, COUNT(*) AS reports FROM sightings GROUP BY region ORDER BY reports DESC, region;

Counting from the end

A negative start counts from the end: SUBSTR(ref_code, -4) is the last 4 characters, '0017'. Handy when the beginning varies in length.

Exercise 2 β€” INSTR: where is it?

location has the form 'City - Spot', but city names have different lengths, so a fixed SUBSTR will not do. INSTR(text, piece) returns the position of a piece (0 if absent):

SELECT location, INSTR(location, ' - ') AS dash_at FROM sightings LIMIT 4;

Feed that position to SUBSTR and the city comes out whatever its length β€” one character less, to leave the space out:

SELECT SUBSTR(location, 1, INSTR(location, ' - ') - 1) AS city, COUNT(*) AS reports FROM sightings GROUP BY city ORDER BY reports DESC, city;

After the separator

SUBSTR(location, INSTR(location, ' - ') + 3) gives the spot: start just after the 3-character separator, and go to the end.

Exercise 3 β€” Count the positions

What does SUBSTR('AZ-2023-0017', 4, 4) return?

Options
β€’ '2023'
β€’ '-202'
β€’ '023-'
β€’ '2023-0017'

Hint : Positions start at 1: A is 1, Z is 2, the dash is 3.

Exercise 4 β€” REPLACE and ||

REPLACE(text, old, new) swaps every occurrence of a piece. Remove the dashes from a code, or turn the separator into a comma:

SELECT ref_code, REPLACE(ref_code, '-', '') AS compact, REPLACE(location, ' - ', ', ') AS address FROM sightings LIMIT 4;

Combine the functions with || to build readable text from pieces:

SELECT TRIM(reporter) || ' reported ' || LOWER(TRIM(codemon)) || ' at ' || SUBSTR(location, 1, INSTR(location, ' - ') - 1) AS report FROM sightings LIMIT 5;

Capitalize

UPPER(SUBSTR(x, 1, 1)) || LOWER(SUBSTR(x, 2)) turns 'PLANTACHU' and 'plantachu' into 'Plantachu' β€” the official spelling. Trim first if the text may start with a space.

Exercise 5 β€” The spot

Complete the query so that it returns the part of location that comes after ' - ' (for example 'North Gate').

Hint : Find the separator's position, then skip its 3 characters.

Exercise 6 β€” Pieces of a city

Match each expression on 'Azure City' with its result.

Items to match
β€’ SUBSTR('Azure City', 1, 5)
β€’ INSTR('Azure City', ' ')
β€’ REPLACE('Azure City', ' ', '_')
β€’ SUBSTR('Azure City', -4)
Definitions
β€’ 'Azure'
β€’ 6
β€’ 'Azure_City'
β€’ 'City'

Exercise 7 β€” Not found

What does INSTR(location, '/') return when location contains no slash?

Options
β€’ 0
β€’ NULL
β€’ -1
β€’ An error

Hint : Positions start at 1, so 0 is free to mean absent.

Exercise 8 β€” Clean report lines

Professor Pi wants the reports reformatted for the archive.

Return, for every sighting, codemon with the official capitalization (first letter upper case, the rest lower case, no stray spaces β€” e.g. 'Plantachu') and address: the location with ' - ' replaced by ', ' (e.g. 'Azure City, North Gate'). Sort by sighting_id.

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