String functions

In short: In SQLite, string functions join text with ||, measure it with LENGTH, cut it with SUBSTR, find a position with INSTR and clean it with REPLACE and TRIM.

Problem

The shelf labels

The shop prints labels for the shelves: the product name and the price, like “White bread (1.20 €)”. The name is in one column and the price in another. You need one column with the finished text, to send straight to the printer.

The idea

Joining with ||

|| joins text. The pieces can be columns, text in quotes or numbers:

SQLdatabase: shop

SELECT name || ' (' || price || ' €)' AS label
FROM products
WHERE category_id = 3;

SELECT name || ' (' || price || ' €)' AS label FROM products WHERE category_id = 3;

▶ RunRestore the queryFresh database

Almost. The number turns into text by itself, but in its shortest form: 1.2, without the zero. A label needs two digits.

printf('%.2f', price) writes the number with exactly two digits after the point:

SQLdatabase: shop

SELECT name || ' (' || printf('%.2f', price) || ' €)' AS label
FROM products
WHERE category_id = 3;

SELECT name || ' (' || printf('%.2f', price) || ' €)' AS label FROM products WHERE category_id = 3;

▶ RunRestore the queryFresh database

Functions can be nested: the result of printf becomes one of the pieces for ||.

More precisely, if you are curious

printf is an SQLite function. Since version 3.38 it is also called format. PostgreSQL has to_char(price, 'FM990.00') for the same job, and MySQL has FORMAT(price, 2). If the page is in another language, with a decimal comma, it is better to keep the number a number and let the program that shows the page format it.

Try it

The email recipient

Email programs show an address as “Maria Ivanova <maria.ivanova@example.com>”. Make such a column for every customer and call it recipient.

SQLdatabase: shop

SELECT name, email FROM customers;

SELECT name, email FROM customers;

Check▶ Run

See it

One city is missing

The courier wants a “name, city” line for every customer:

SQLdatabase: shop

SELECT name, city, name || ', ' || city AS contact
FROM customers
WHERE id BETWEEN 10 AND 13;

SELECT name, city, name || ', ' || city AS contact FROM customers WHERE id BETWEEN 10 AND 13;

▶ RunRestore the queryFresh database

1. Why is the contact line NULL for Boris Mitev?

Because || works only with numbersBecause his city is NULL, and text joined with NULL is NULL tooBecause his name is too long

Check

When a column can be NULL, the joined text can be NULL too. How to put words in place of the missing city comes in the lesson on CASE and COALESCE.

The idea

Parts of the text

The text functions you will use most often:

• LENGTH(t): how many characters it has.
• SUBSTR(t, from, count): a piece of the text. Counting starts at 1.
• INSTR(t, part): the position of the part, or 0 if it is not there.
• UPPER(t) and LOWER(t): capital and small letters.
• REPLACE(t, what, with), and TRIM(t), which removes the spaces at the start and the end.

SQLdatabase: shop

SELECT name, LENGTH(name), SUBSTR(name, 1, 4), INSTR(name, ' ')
FROM products
WHERE category_id = 4;

SELECT name, LENGTH(name), SUBSTR(name, 1, 4), INSTR(name, ' ') FROM products WHERE category_id = 4;

▶ RunRestore the queryFresh database

SUBSTR and INSTR together cut text at exactly the right place. The domain of an email is everything after the @:

SQLdatabase: shop

SELECT email,
       INSTR(email, '@') AS at,
       SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM customers
WHERE city = 'Varna';

SELECT email, INSTR(email, '@') AS at, SUBSTR(email, INSTR(email, '@') + 1) AS domain FROM customers WHERE city = 'Varna';

▶ RunRestore the queryFresh database

TRIM is needed when the data comes from a form or a spreadsheet and someone left a space:

SQLdatabase: shop

SELECT LENGTH('  White bread '), LENGTH(TRIM('  White bread '));

SELECT LENGTH(' White bread '), LENGTH(TRIM(' White bread '));

▶ RunRestore the queryFresh database

Try it

Initials

The loyalty cards need the customer's initials: “M. I.” for Maria Ivanova. Show the name and the initials in a column called initials.

SQLdatabase: shop

SELECT name, SUBSTR(name, 1, 1) FROM customers;

SELECT name, SUBSTR(name, 1, 1) FROM customers;

Check▶ Run

See it

UPPER and other alphabets

The website wants an address for every product, like white-bread: small letters, a hyphen instead of a space. REPLACE and LOWER do it:

SQLdatabase: shop

SELECT name, LOWER(REPLACE(name, ' ', '-')) AS slug
FROM products
WHERE category_id = 3;

SELECT name, LOWER(REPLACE(name, ' ', '-')) AS slug FROM products WHERE category_id = 3;

▶ RunRestore the queryFresh database

It works here because the names use only the letters A to Z. The built-in UPPER and LOWER in SQLite change only those plain Latin letters. A letter with an accent, or a whole other alphabet like Cyrillic or Greek, stays as it is:

SQLdatabase: shop

SELECT UPPER('cafe'), UPPER('café');

SELECT UPPER('cafe'), UPPER('café');

▶ RunRestore the queryFresh database

There is no error, so it is easy to miss. The same goes for LIKE: it ignores case only for the letters A to Z.

More precisely, if you are curious

PostgreSQL and MySQL with UTF-8 text change other alphabets correctly. SQLite has an extension (ICU) that teaches UPPER, LOWER and LIKE every alphabet, but it is not available in the browser here. That is why programs that use SQLite often keep the search text in a separate column, already in small letters, computed by the program.

Exercise

Two tasks

1. A label fits 15 characters. Which products have a longer name? Show the name and its length in a column called length.

The products with a name over 15 characters:

SQLdatabase: shop

Check▶ Run

2. The new login system needs a username: the part of the email before the @. Show the email and the username in a column called username.

The email and username of every customer:

SQLdatabase: shop

SELECT email, INSTR(email, '@') FROM customers;

SELECT email, INSTR(email, '@') FROM customers;

Check▶ Run

Test

Check yourself

1. What does 'White' || ' ' || 'bread' give?

An error: || means “or”WhitebreadWhite bread

2. What does SUBSTR('Kashkaval', 1, 4) return?

Kashashkaval

3. What does INSTR('Yoghurt', ' ') return?

0NULL-1

4. What does UPPER('café') give in SQLite?

An errorCAFéCAFÉ

Check

Start the lesson over

Related lessons

• Builds on: WHERE: filtering rows · IN, BETWEEN and LIKE · NULL: no value · Arithmetic in a query
• Continues in: CASE and missing values · Trees and sequences: WITH RECURSIVE · Slow queries · SQL injection and parameters
• Tasks on this topic: Expressions and functions