С помощью функций мы также можем вырезать из текста определенное количество символов. Из приведенного выше примера видно, что почтовые индексы США в таблице customers не единообразны. Некоторые из них содержат дефис и дополнительный четырехзначный номер, который почтовая служба США называет кодом ZIP +4. В одном из почтовых индексов отсутствует четвертая цифра почтового кода ZIP +4.
На мой взгляд,
обнаружение ошибок или несоответствий в базе данных неизбежно. Способность предусматривать ошибки и исключения и обрабатывать их — часть процесса обучения и бесценный навык. Поля часто содержат орфографические ошибки, неправильное количество символов или другие несоответствия. В дальнейшем вы научитесь предусматривать и обрабатывать ошибки по мере их появления.
Чтобы почтовый индекс был единообразным, мы можем использовать функции для удаления дефиса и лишних чисел. Благодаря усовершенствованной системе маршрутизации почтовой службы США дополнительные номера в почтовых индексах США (сверх начальных пяти) не требуются.
Чтобы использовать функции редактирования строк, нам необходимо больше узнать о хранении строк в базе данных. Символы в каждой строке нумеруются, начиная с 1. Это важно при работе со строками, так как позволяет указать фрагмент строки, используя порядковый номер символа.
Мы используем функцию LENGTH() в таком поле, как PostalCode (таблица customers), и видим, что длина каждого кода может быть вычислена.
SELECT
PostalCode,
LENGTH(PostalCode) AS [Postal Code Length]
FROM
customers
WHERE
Country = "USA"
Рис. 91
Анализируя результаты (рис. 91), мы видим, что почтовые индексы различаются размером строк. Минимальная необходимая длина почтового индекса США — пять цифр. С помощью функции SUBSTR() удалим всю информацию после пятой позиции в строке.
Существует два вида функции SUBSTR(): SUBSTR(X,Y) и SUBSTR(X,Y,Z). Чтобы посмотреть краткое описание функции, введите в браузере SUBSTR(.
Примечание
Чтобы DB Browser отображал описание функции SUBSTR(X,Y,Z), необходимо ввести SUBSTR(X,Y, что означает, что вас интересует версия функции с тремя аргументами.
Рис. 92
Если для удаления дополнительных данных из почтовых индексов США мы используем функцию SUBSTR(X,Y,Z), аргумент X будет соответствовать полю PostalCode, а аргумент Y — начальной позиции строки. В данном случае мы хотим, чтобы первые пять чисел остались, поэтому выберем 1. Аргумент Z указывает количество символов, которые функция вернет из начальной позиции, в нашем случае оно равно 5.
Если мы выберем только адреса США, а затем добавим функцию SUBSTR(X,Y,Z) с псевдонимом, мы получим следующее:
SELECT
PostalCode,
SUBSTR(PostalCode,1,5) AS [Five Digit Postal Code]
FROM
customers
WHERE
Country = "USA"
Анализируя результат (рис. 93), мы видим, что все наши почтовые индексы содержат только первые пять цифр.
Примечание
Почтовые индексы, которые не содержат дополнительных номеров, этот запрос не затрагивает.
Мы также можем разделить данные, используя функцию SUBSTR(). Обратите внимание, что версия функции SUBSTR(), принимающей два аргумента, возвращает все символы до конца строки, начиная с позиции Y. Если позиция Y не начинается с 1, мы можем указать функции, чтобы она возвращала только символы, начиная с позиции Y и далее.
Рис. 93
Практическое задание
• Повторите предыдущий запрос, но в блок SELECT добавьте дополнительное поле. Используйте функцию SUBSTR(X,Y), возвращающую только последние четыре цифры почтового индекса (там, где они есть) с псевдонимом ZIP + 4 Code.
Дополнительные строковые функции
В нашей книге перечислена лишь часть функций. Как упоминалось в начале этой главы, полный список функций, поддерживаемых SQLite, можно найти на сайте SQLite. Альтернативный способ изучить новые функции — ввести каждую, прочитать всплывающую подсказку и попытаться понять, как функция работает. Прежде чем приступить к функциям даты и времени и агрегатным функциям, мы изучим еще две полезные строковые функции.
Рис. 94
Функция UPPER(X) возвращает копию входной строки X, в которой все символы ASCII в нижнем регистре переведены в верхний регистр. Функция LOWER() переводит все символы аргумента в нижний регистр.
Рассмотрим пример использования этих функций:
SELECT
FirstName as [First Name Unmodified],
UPPER(FirstName) as [First Name in UPPERCASE],
LOWER(FirstName) as [First Name in lowercase],
UPPER(FirstName) || ' ' || UPPER(LastName) AS [Full Name in UPPERCASE]
FROM
customers
В данном запросе в качестве аргумента функций UPPER() и LOWER() выступает поле FirstName. Также для обозначения результата мы использовали псевдоним FullName, чтобы показать, что вы можете объединить два поля после выполнения функций.
Примечание
Функции UPPER() и LOWER() работают только с символами ASCII. Любые символы, изначально отформатированные в Юникоде, остаются без изменений.
Рис. 95
Практическое задание
• Используйте функцию SUBSTR() вместе с функциями UPPER() и LOWER() и создайте список клиентов: фамилия должна быть выведена первой и всеми заглавными буквами, а от имени следует оставить только первую букву.
Функции даты и времени
Функции даты и времени позволяют управлять данными, хранящимися в различных форматах даты и времени. В базе данных sTunes информация о дате хранится в формате DATETIME: YYYY-MM-DDHH: MM:SS. Хотя формат позволяет вводить временной код, он не используется в нашей базе данных — все временные коды пусты и отображают 00:00:00. Поэтому, чтобы убрать временной код и оставить только информацию о дате, мы использовали функцию DATE() (см. главу 5). Поскольку в разных базах данных информация о дате может храниться по-разному, важно знать, как преобразовать один формат в другой. С датами мы можем сделать гораздо больше, чем просто изменить их формат. Например, чтобы вычислить возраст сотрудников, мы можем посчитать разницу между любой заданной и текущей датой, поскольку в таблице employees имеется поле BirthDate (Дата рождения).
Чтобы рассчитать возраст сотрудников, рассмотрим функцию STRFTIME(), также известную как функция времени в строковом формате. Она позволяет отформатировать информацию о времени и дате в виде текстовой строки. Для корректной работы функции STRFTIME() требуется как минимум информация двух видов. Необходимо указать желаемый формат (его называют спецификацией преобразования) и строку времени для форматирования. Строку времени можно ввести вручную или использовать поле DATETIME. В качестве аргумента строка времени также может использовать функцию NOW. Третий аргумент, модификатор, необязательный и может применяться для дискретного сдвига даты вперед или назад и выполнения некоторых других функций.
Рис. 96
Примечание
Функцию NOW иногда называют недетерминированной функцией. Это означает, что результирующие данные, возвращаемые этой функцией, будут разными при каждом ее вызове, поскольку дата и/или время будут разными при каждом вызове. Функция STRFTIME() и большинство других функций, описанных в этой книге, — детерминированные. То есть они дают один и тот же результат каждый раз, когда используются с одними и теми же аргументами. Функцию NOW необходимо постоянно обновлять, чтобы она оставалась точной и результат соответствовал времени вашего компьютера.
Примечание
Функция STRFTIME() способна выполнять различные преобразования времени и дат. Если вы пока не совсем понимаете значения всех аргументов и выполняемых преобразований, не беспокойтесь. Главное то, что функция STRFTIME() принимает данные в формате времени и даты и использует ключевые слова для возврата определенных пользователем фрагментов даты.
Рис. 97
Рис. 98
Рис. 99
Мы можем использовать любые символы внутри одинарных кавычек при условии, что всю строку заключаем в кавычки.
SELECT
STRFTIME('The Year is: %Y The Day is: %d The Month is %m', '2011-05-22') AS [Text with Conversion Specifications]
Рис. 100
НАПОМИНАНИЕ
Спецификация в функции преобразования всегда начинается с символа %, за которым следует чувствительный к регистру буквенный символ. Использование, например, %M (верхний регистр) вместо %m даст нам минуты вместо месяцев.
Давайте рассмотрим работу функции STRFTIME() на примере вычисления возраста сотрудников. Первое, что необходимо сделать, это указать необходимый формат. Поскольку BirthDate (Дата рождения) имеет тип данных DATETIME, а время в нашей базе данных не указано, для простоты опустим временные коды. Чтобы узнать возраст сотрудников, вычислим разницу во времени между датой рождения каждого сотрудника и текущей датой. Текущую дату можно получить, используя функцию NOW.
SELECT
LastName,
FirstName,
STRFTIME('%Y-%m-%d',BirthDate) AS [Birthday No Timecode],
STRFTIME('%Y-%m-%d','now') — STRFTIME('%Y-%m-%d', BirthDate) AS [Age]
FROM
employees
ORDER BY
Age
Рис. 101
Мы можем использовать функцию STRFTIME() так же, как мы использовали функцию DATE() для удаления временных кодов. Затем, чтобы узнать возраст сотрудников, необходимо получить разницу между двумя функциями strftime.
Практические задания
• Компания sTunes отмечает дни рождения сотрудников первого числа каждого месяца. Создайте для отдела кадров таблицу, отображающую имена сотрудников, дни рождения и день празднования.
• Отдел кадров компании sTunes сообщил нам, что возраст сотрудников — это тема весьма деликатная. Перепишите этот запрос, чтобы в результате получить количество лет работы каждого сотрудника в компании.
• Какой сотрудник проработал в компании дольше всех?