SQL: быстрое погружение

обнаружение ошибок или несоответствий в базе данных неизбежно. Способность предусматривать ошибки и исключения и обрабатывать их — часть процесса обучения и бесценный навык. Поля часто содержат орфографические ошибки, неправильное количество символов или другие несоответствия. В дальнейшем вы научитесь предусматривать и обрабатывать ошибки по мере их появления.

Чтобы почтовый индекс был единообразным, мы можем использовать функции для удаления дефиса и лишних чисел. Благодаря усовершенствованной системе маршрутизации почтовой службы США дополнительные номера в почтовых индексах США (сверх начальных пяти) не требуются.

Чтобы использовать функции редактирования строк, нам необходимо больше узнать о хранении строк в базе данных. Символы в каждой строке нумеруются, начиная с 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() и создайте список клиентов: фамилия должна быть выведена первой и всеми заглавными буквами, а от имени следует оставить только первую букву.

Функции даты и времени