SUBSTR, INSTR and REPLACE
The full text of this chapter, to read or revisit. To practise, do the exercises.
βΆ Do the exercisesExercise 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?
'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.
SUBSTR('Azure City', 1, 5)INSTR('Azure City', ' ')REPLACE('Azure City', ' ', '_')SUBSTR('Azure City', -4)'Azure'6'Azure_City''City'Exercise 7 β Not found
What does INSTR(location, '/') return when location contains no slash?
0NULL-1Hint : 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.
-- Write your query below