Text Functions

Functions work with text too. UPPER(x) writes a text in capitals, LOWER(x) in lowercase, and LENGTH(x) tells you how many characters it has:

SELECT name, UPPER(name) AS upper_name, LENGTH(name) AS characters
FROM customers
LIMIT 3;

LENGTH counts every character: letters, digits, spaces and punctuation. Emma Clark has 10 of them, the space included. And LOWER('Moon Farm') gives moon farm.

Where is that useful? A banner on the shop's homepage shows titles in capitals, a form checks that a name isn't too long for a shipping label, and LOWER helps when you compare texts and don't know how they're written: LOWER(genre) is rpg whether the table says RPG, Rpg or rpg. You'll use that in WHERE later in this lesson.

Functions don't change the data in the table, only what the query shows. After the query above, the table still says Emma Clark, just as before. That's true of every query: it only reads the data.

And NULL stays NULL: the length of a missing comment isn't 0, it's NULL. A function that gets no value has nothing to work with, so it gives back no value either. You'll learn how to replace NULL with something else soon.

Typical mistake: the name of a column in quotes. UPPER('title') doesn't work with the column title, it writes the text 'title' in capitals, so every row says TITLE. Text in single quotes is always just text; write the names of columns without quotes.