Работа со строковыми данными в SQL: строки и функции для их обработки
Назад<<< | Содержание | Вперёд>>> |
Функции для работы со строками в SQL позволяют манипулировать текстовыми данными и получать результаты запросов, в которых текстовые данные приобретают преобразованный вид, отличающийся от того, в котором они хранятся в таблицах базы данных. Следует подчеркнуть, что эти функции не вносят изменений в исходные данные; с помощью их изменённые данные получают лишь на выходе запроса. Функций для работы со строками в SQL насчитывается несколько десятков. Мы рассмотрим наиболее употребительные их них.
Функции для работы со строками имеют следуюший синтаксис:
Если использовать запрос без необязательной секции WHERE, то действие функции затронет все строки таблицы. Если использовать секцию WHERE, то условие задаёт те строки, к которым применяется функции.
В примерах работаем с базой данных библиотеки и её таблицей "Книга в выдаче" (BOOKINUSE). Операции будут относиться к столбцам Author (автор книги) и Title (название книги).
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.
Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными - в файле по этой ссылке.
Author | Title | Pubyear | Inv_No | Customer_ID |
Толстой | Война и мир | 2005 | 28 | 65 |
Чехов | Вишневый сад | 2000 | 17 | 31 |
Чехов | Избранные рассказы | 2011 | 19 | 120 |
Чехов | Вишневый сад | 1991 | 5 | 65 |
Ильф и Петров | Двенадцать стульев | 1985 | 3 | 31 |
Маяковский | Поэмы | 1983 | 2 | 120 |
Пастернак | Доктор Живаго | 2006 | 69 | 120 |
Толстой | Воскресенье | 2006 | 77 | 47 |
Толстой | Анна Каренина | 1989 | 7 | 205 |
Пушкин | Капитанская дочка | 2004 | 25 | 47 |
Гоголь | Пьесы | 2007 | 81 | 47 |
Чехов | Избранные рассказы | 1987 | 4 | 205 |
Пушкин | Сочинения, т.1 | 1984 | 6 | 47 |
Пастернак | Избранное | 2000 | 137 | 18 |
Пушкин | Сочинения, т.2 | 1984 | 8 | 205 |
NULL | Наука и жизнь 9 2018 | 2019 | 127 | 18 |
Чехов | Ранние рассказы | 2001 | 171 | 31 |
Функция SQL CONCAT()
Функция CONCAT() возвращает новую строку, в которой объединены (сцеплены) аргументы, указанные в скобках (str1, str2, ..., strN). Аргументов у этой функции может быть не менее двух, а максимум аргументов не ограничен.
Пример 1. Получить в одном столбце авторов и названия всех выданных книг. Чтобы автор и название книги не слипались, вторым аргументом функции CONCAT указываем пробел ' '. Пишем следующий запрос:
Этот запрос не содержит секции WHERE, поэтому функция применяется ко всем строкам таблицы. Результат выполнения запроса:
CONCAT (Author, ' ', Title) |
Толстой Война и мир |
Чехов Вишневый сад |
Чехов Избранные рассказы |
Чехов Вишневый сад |
Ильф и Петров Двенадцать стульев |
Маяковский Поэмы |
Пастернак Доктор Живаго |
Толстой Воскресенье |
Толстой Анна Каренина |
Пушкин Капитанская дочка |
Пушкин Сочинения т. 1 |
Пушкин Сочинения т. 2 |
Гоголь Пьесы |
Чехов Избранные рассказы |
Наука и жизнь 9 2018 |
Пастернак Избранное |
Чехов Ранние рассказы |
Функция MySQL INSERT() и функция MS SQL Server STUFF()
Названия этих функций различаются в разных СУБД, но выполняют они одну задачу: возвращают новую строку,
являющуюся результатом того, что начиная с определённой позиции исходной строки указанное число символов
заменено некоторой новой подстрокой.
Написание функций с аргументами:
INSERT(строка, позиция, длина, вставляемая подстрока);
STUFF(строка, позиция, длина, вставляемая подстрока).
Пример 2. Предположим, что библиотекари устроили друг другу викторину и делают из некоторых записей таблицы своеобразные загадки. Требуется вывести автора книги "Двенадцать стульев" так, чтобы оно читалось не как "Ильф и Петров", а как "Ильф и его соавтор". Пишем следующий запрос на MySQL:
Результат выполнения запроса:
New_Author |
Ильф и его соавтор |
Пишем запрос на MS SQL Server (с предваряющей конструкцией USE library;):
Результат будет аналогичным.
Функция SQL REPLACE()
Эта функция возвращает новую строку, являющуюся результатом того, что в исходной строке str все
подстроки substr1 заменены на подстроки substr2. Написание функции:
REPLACE(str, substr1, substr2).
Пример 3. Викторина в библиотеке продолжается. Требуется заменить фамилию автора "Чехов" на "Мастер короткого рассказа". Пишем следующий запрос на MySQL:
Так как запрос написан без секции WHERE, замена произойдёт во всех записях, соответствующих записям исходной таблицы. Результат выполнения запроса:
REPLACE (Author, 'Чехов' 'Мастер короткого рассказа') |
Толстой |
Мастер короткого рассказа |
Мастер короткого рассказа |
Мастер короткого рассказа |
Ильф и Петров |
Маяковский |
Пастернак |
Толстой |
Толстой |
Пушкин |
Гоголь |
Мастер короткого рассказа |
Пастернак |
Функция SQL LOWER()
Эта функция возвращает новую строку str, в которой все символы исходной строки переведены в нижний
регистр. Написание функции:
LOWER(str).
Пример 4. Пусть требуется написать фамилии всех авторов в нижнем регистре. Пишем следующий запрос:
Начало и конец таблицы, являющейся результатом выполнения этого запроса будет выглядеть так:
LOWER(Author) |
толстой |
чехов |
чехов |
чехов |
... |
гоголь |
чехов |
пастернак |
Функция SQL UPPER()
Эта функция возвращает новую строку str, в которой все символы исходной строки переведены в верхний
регистр. Написание функции:
UPPER(str).
Пример 5. Пусть требуется написать фамилии всех авторов в верхнем регистре. Пишем следующий запрос:
Начало и конец таблицы, являющейся результатом выполнения этого запроса будет выглядеть так:
LOWER(Author) |
ТОЛСТОЙ |
ЧЕХОВ |
ЧЕХОВ |
ЧЕХОВ |
... |
ГОГОЛЬ |
ЧЕХОВ |
ПАСТЕРНАК |
Примеры запросов к базе данных "Библиотека" есть также в уроках по операторам GROUP BY, IN, предикату EXISTS и функции COALESCE.
Функция MySQL LENGTH() и функция MS SQL Server LEN()
Названия этих функций различаются в разных СУБД, но выполняют они одну задачу: возвращают длину строки
str.
Написание функции:
LENGTH(str);
LEN(str).
Пример 6. Пусть требуется узнать длину фамилий авторов выданных книг. Пишем следующий запрос:
Начало и конец таблицы, являющейся результатом выполнения этого запроса будет выглядеть так:
Author | LENGTH(Author) |
Толстой | 7 |
Чехов | 5 |
Чехов | 5 |
Чехов | 5 |
... | ... |
Гоголь | 6 |
Чехов | 5 |
Пастернак | 9 |
Ещё несколько употребительных функций SQL для работы со строками
LPAD(str, len, str_plus) (MySQL, Oracle) - возвращает строку str, к которой слева до длины len добавлена строка str_plus.
RPAD(str, len, str_plus) (MySQL, Oracle) - возвращает строку str, к которой справа до длины len добавлена строка str_plus.
LTRIM(str) (MySQL, MS SQL Server, Oracle) - возвращает строку str, из которой удалены все пробелы слева.
RTRIM(str) (MySQL, MS SQL Server, Oracle) - возвращает строку str, из которой удалены все пробелы справа.
TRIM(str) (MySQL, Oracle) - возвращает строку str, из которой удалены все пробелы слева и справа.
LEFT(str, len) (MySQL, MS SQL Server) - возвращает len символов слева строки str.
RIGHT(str, len) (MySQL, MS SQL Server) - возвращает len символов справа строки str.
Поделиться с друзьями
Назад<<< | Содержание | Вперёд>>> |