Текстови функции

Накратко: В SQLite текстовите функции слепват текст с ||, мерят дължината му с LENGTH, режат го със SUBSTR, търсят в него с INSTR и го чистят с REPLACE и TRIM.

Проблем

Етикетите на рафта

Магазинът печата етикети за рафтовете: името на продукта и цената, като „Бял хляб (1,20 €)“. Името е в една колона, цената в друга. Трябва ти една колона с готовия текст, за да я пратиш направо на принтера.

Идеята

Слепване с ||

|| слепва текст. Парчетата може да са колони, текст в кавички или числа:

SQLбаза: магазин

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

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

▶ ИзпълниВърни заявкатаНова база

Почти. Числото се превръща в текст от само себе си, но в най-краткия си вид: 1.2, с точка и без нулата. За етикет трябват две цифри и запетая.

printf('%.2f', price) пише числото с точно две цифри след точката: 1.20. REPLACE(текст, какво, с какво) сменя точката със запетая:

SQLбаза: магазин

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

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

▶ ИзпълниВърни заявкатаНова база

Функциите се влагат една в друга: REPLACE получава готовия текст от printf.

По-точно, за любопитните

printf е функция на SQLite. От версия 3.38 тя се казва и format. В PostgreSQL за същото има to_char(price, 'FM990.00'), а в MySQL FORMAT(price, 2). Ако страницата е на няколко езика, по-добре е числото да остане число, а програмата, която показва страницата, да го оформи.

Пробвай

Получателят на писмото

Пощенските програми показват адреса като „Мария Иванова <maria.ivanova@example.com>“. Направи такава колона за всеки клиент и я наречи recipient.

SQLбаза: магазин

SELECT name, email FROM customers;

SELECT name, email FROM customers;

Провери▶ Изпълни

Виж го

Един град липсва

Куриерът иска ред „име, град“ за всеки клиент:

SQLбаза: магазин

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;

▶ ИзпълниВърни заявкатаНова база

1. Защо за Борис Митев контактът е NULL?

Защото градът му е NULL, а текст, слепен с NULL, също е NULLЗащото || работи само с числаЗащото името му е твърде дълго

Провери

Когато колоната може да е NULL, слепеният текст също може да е NULL. Как да сложиш думи на мястото на липсващия град, ще видиш в урока за CASE и COALESCE.

Идеята

Части от текста

Функциите за текст, които ще ползваш най-често:

• LENGTH(t): колко знака има.
• SUBSTR(t, от, колко): парче от текста. Броенето започва от 1.
• INSTR(t, част): на кое място е частта, или 0, ако я няма.
• UPPER(t) и LOWER(t): главни и малки букви.
• REPLACE(t, какво, с какво) и TRIM(t), който маха интервалите в началото и в края.

SQLбаза: магазин

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;

▶ ИзпълниВърни заявкатаНова база

SUBSTR и INSTR заедно режат текст на точното място. Домейнът на имейла е всичко след @:

SQLбаза: магазин

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

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

▶ ИзпълниВърни заявкатаНова база

TRIM трябва, когато данните идват от формуляр или от електронна таблица и някой е оставил интервал:

SQLбаза: магазин

SELECT LENGTH('  Бял хляб '), LENGTH(TRIM('  Бял хляб '));

SELECT LENGTH(' Бял хляб '), LENGTH(TRIM(' Бял хляб '));

▶ ИзпълниВърни заявкатаНова база

Пробвай

Инициали

За картите за отстъпка трябват инициалите на клиента: „М. И.“ за Мария Иванова. Покажи името и инициалите в колона initials.

SQLбаза: магазин

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

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

Провери▶ Изпълни

Виж го

LOWER и кирилицата

Сайтът иска адрес за всеки продукт от вида бял-хляб: малки букви, тире вместо интервал. Пусни:

SQLбаза: магазин

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;

▶ ИзпълниВърни заявкатаНова база

Тиретата са там, но „Бял“ е с главно „Б“. Вградените UPPER и LOWER в SQLite сменят само латинските букви:

SQLбаза: магазин

SELECT UPPER('kafe'), UPPER('кафе');

SELECT UPPER('kafe'), UPPER('кафе');

▶ ИзпълниВърни заявкатаНова база

Същото видя при LIKE: за SQLite „Б“ и „б“ са два различни знака. Грешка няма, затова това често се пропуска.

По-точно, за любопитните

PostgreSQL и MySQL с текст в UTF-8 сменят правилно и кирилицата. В SQLite има разширение (ICU), което учи UPPER, LOWER и LIKE на всички азбуки, но в браузъра тук го няма. Затова програмите, които ползват SQLite, често пазят текста за търсене в отделна колона, вече с малки букви, сметнати в програмата.

Упражнение

Две задачи

1. На етикета се побират 15 знака. Кои продукти имат по-дълго име? Покажи името и дължината му в колона length.

Продуктите с име над 15 знака:

SQLбаза: магазин

Провери▶ Изпълни

2. Новата система за вход иска потребителско име: частта от имейла преди @. Покажи имейла и потребителското име в колона username.

Имейлът и потребителското име на всеки клиент:

SQLбаза: магазин

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

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

Провери▶ Изпълни

Тест

Провери се

1. Какво дава 'Бял' || ' ' || 'хляб'?

Бял хлябГрешка: || е „или“Бялхляб

2. Какво връща SUBSTR('Кашкавал', 1, 4)?

Кашкавалашка

3. Какво връща INSTR('Сирене', ' ')?

-1NULL0

4. Какво дава UPPER('кафе') в SQLite?

ГрешкакафеКАФЕ

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: WHERE: филтриране на редове · IN, BETWEEN и LIKE · NULL: няма стойност · Сметки в заявката
• Продължава в: CASE и липсващите стойности · Дървета и редици: WITH RECURSIVE · Бавни заявки · SQL injection и параметрите
• Задачи по темата: Изрази и функции