Learn SQL: Text functions

Working with text

"Why are there two kinds of novels in my overview?" Sanne frowns. Someone once typed `Novel` with a capital, someone else `novel` without. Time to learn how to tame text.

Functions on text

A function takes a value and gives back a new one, row by row. UPPER puts text in capitals, LOWER in small letters, and LENGTH counts the characters.

The table itself doesn't change: only the result does.

SELECT title, UPPER(title) AS loud, LENGTH(title) AS chars FROM books

Result
title	loud	chars
De stille rivier	DE STILLE RIVIER	16
Nachtwacht in Brugge	NACHTWACHT IN BRUGGE	20
Het laatste licht	HET LAATSTE LICHT	17
Draken van het Noorden	DRAKEN VAN HET NOORDEN	22
Koken met restjes	KOKEN MET RESTJES	17
The Glass Harbour	THE GLASS HARBOUR	17
Zomer in Zeeland	ZOMER IN ZEELAND	16
De code van Mercator	DE CODE VAN MERCATOR	20

Cleaning up messy data

To SQLite, novel and Novel are two different values. Grouping by genre therefore gives two little groups of novels (you can see the problem below).

Group by LOWER(genre) and they merge. You can simply use a function in GROUP BY.

SELECT LOWER(genre) AS genre, COUNT(*) AS total FROM books GROUP BY LOWER(genre)

Result
genre	total
comic	2
fantasy	3
non-fiction	3
novel	7
thriller	5

SELECT genre, COUNT(*) AS total FROM books GROUP BY genre

Without LOWER
genre	total
Novel	2
THRILLER	1
comic	2
fantasy	3
non-fiction	3
novel	5
thriller	4

Gluing, cutting and polishing

|| glues text together. SUBSTR(text, start, length) cuts out a piece, counting from 1. REPLACE(text, old, new) swaps text, and TRIM removes spaces at the start and the end.

You can glue numbers on too: SQLite turns them into text.

SELECT author || ': ' || title AS label, SUBSTR(isbn, 1, 3) AS prefix, REPLACE(isbn, '-', '') AS no_dashes FROM books

Result
label	prefix	no_dashes
Lies Verbeke: De stille rivier	978	9789044110013
Tom Janssens: Nachtwacht in Brugge	978	9789044110020
Ann De Wilde: Het laatste licht	978	9789044110037
Kris Maes: Draken van het Noorden	978	9789044110044
Sara Peeters: Koken met restjes	978	9789044110051
Emily Hart: The Glass Harbour	978	9781840010068
Lies Verbeke: Zomer in Zeeland	978	9789044110075
Tom Janssens: De code van Mercator	978	9789044110082

Try it yourself

Make labels for the shelves. Show each book's title with its year after it in brackets, for example Het eiland (2011).

Ready for more?

The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.

Start the lesson