Parts of Text

Often, you need just a part of a text: the year of a date, the first letters of a name, the domain of an email. SUBSTR(text, start, length) (from “substring”) cuts out length characters, starting with the character at the position start. The positions are counted from 1:

SELECT SUBSTR('Dragon Forge', 1, 6) AS first_word, SUBSTR('Dragon Forge', 8) AS rest;

Without the third argument, SUBSTR goes on to the end of the text. And since dates are texts, SUBSTR(released, 1, 4) is the year in which a game came out and SUBSTR(released, 6, 2) the month.

INSTR(text, part) finds where a part of a text is: it gives the position where the part starts, or 0 when the text doesn't contain it. INSTR('emma.clark@mail.com', '@') is 11. Together with SUBSTR, it can cut a text at a character, wherever it is. The user name of an email is everything before the @:

SELECT email,
       INSTR(email, '@') AS at_position,
       SUBSTR(email, 1, INSTR(email, '@') - 1) AS username
FROM customers
ORDER BY id
LIMIT 3;

Read the last column from the inside out: INSTR finds the @, - 1 steps one character back, and SUBSTR takes that many characters from the start.

REPLACE(text, old, new) replaces every occurrence of one text with another. For its hashtags, the shop removes the spaces from titles by replacing them with an empty text:

SELECT title, '#' || REPLACE(title, ' ', '') AS hashtag
FROM games
LIMIT 3;

TRIM(text) removes the spaces at the start and at the end of a text; LTRIM and RTRIM remove them only on the left or only on the right. The shop's data is tidy, but texts that people type into forms often aren't, and 'Moon Farm ' isn't equal to 'Moon Farm'. To see the spaces, put brackets around the text:

SELECT '[' || '  Moon Farm ' || ']' AS untrimmed,
       '[' || TRIM('  Moon Farm ') || ']' AS trimmed;

With a second argument, TRIM removes other characters instead of spaces: TRIM('...Wow!!', '.!') is Wow.

Typical mistake: counting from 0, as many programming languages do. SUBSTR(released, 0, 4) gives just three characters, such as 202, because position 0 is before the text starts. In SQL, the first character is at position 1.