Базы данных
и язык SQL

Работа со строковыми данными в SQL: строки и функции для их обработки

Функции для работы со строками в SQL позволяют манипулировать текстовыми данными и получать результаты запросов, в которых текстовые данные приобретают преобразованный вид, отличающийся от того, в котором они хранятся в таблицах базы данных. Следует подчеркнуть, что эти функции не вносят изменений в исходные данные; с помощью их изменённые данные получают лишь на выходе запроса. Функций для работы со строками в SQL насчитывается несколько десятков. Мы рассмотрим наиболее употребительные их них.

Функции для работы со строками имеют следуюший синтаксис:

SELECT ИМЯ_ФУНКЦИИ (АРГУМЕНТЫ) FROM ИМЯ_ТАБЛИЦЫ [WHERE УСЛОВИЕ]

Если использовать запрос без необязательной секции WHERE, то действие функции затронет все строки таблицы. Если использовать секцию WHERE, то условие задаёт те строки, к которым применяется функции.

В примерах работаем с базой данных библиотеки и её таблицей "Книга в выдаче" (BOOKINUSE). Операции будут относиться к столбцам Author (автор книги) и Title (название книги).

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными - в файле по этой ссылке.

AuthorTitlePubyearInv_NoCustomer_ID
ТолстойВойна и мир20052865
ЧеховВишневый сад20001731
ЧеховИзбранные рассказы201119120
ЧеховВишневый сад1991565
Ильф и ПетровДвенадцать стульев1985331
МаяковскийПоэмы19832120
ПастернакДоктор Живаго200669120
ТолстойВоскресенье20067747
ТолстойАнна Каренина19897205
ПушкинКапитанская дочка20042547
ГогольПьесы20078147
ЧеховИзбранные рассказы19874205
ПушкинСочинения, т.11984647
ПастернакИзбранное200013718
ПушкинСочинения, т.219848205
NULLНаука и жизнь 9 2018201912718
ЧеховРанние рассказы200117131

Функция SQL CONCAT()

Функция CONCAT() возвращает новую строку, в которой объединены (сцеплены) аргументы, указанные в скобках (str1, str2, ..., strN). Аргументов у этой функции может быть не менее двух, а максимум аргументов не ограничен.

Пример 1. Получить в одном столбце авторов и названия всех выданных книг. Чтобы автор и название книги не слипались, вторым аргументом функции CONCAT указываем пробел ' '. Пишем следующий запрос:

SELECT CONCAT (Author, ' ', Title) FROM BOOKINUSE

Этот запрос не содержит секции WHERE, поэтому функция применяется ко всем строкам таблицы. Результат выполнения запроса:

CONCAT (Author, ' ', Title)
Толстой Война и мир
Чехов Вишневый сад
Чехов Избранные рассказы
Чехов Вишневый сад
Ильф и Петров Двенадцать стульев
Маяковский Поэмы
Пастернак Доктор Живаго
Толстой Воскресенье
Толстой Анна Каренина
Пушкин Капитанская дочка
Пушкин Сочинения т. 1
Пушкин Сочинения т. 2
Гоголь Пьесы
Чехов Избранные рассказы
Наука и жизнь 9 2018
Пастернак Избранное
Чехов Ранние рассказы

Функция MySQL INSERT() и функция MS SQL Server STUFF()

Названия этих функций различаются в разных СУБД, но выполняют они одну задачу: возвращают новую строку, являющуюся результатом того, что начиная с определённой позиции исходной строки указанное число символов заменено некоторой новой подстрокой. Написание функций с аргументами:
INSERT(строка, позиция, длина, вставляемая подстрока);
STUFF(строка, позиция, длина, вставляемая подстрока).

Пример 2. Предположим, что библиотекари устроили друг другу викторину и делают из некоторых записей таблицы своеобразные загадки. Требуется вывести автора книги "Двенадцать стульев" так, чтобы оно читалось не как "Ильф и Петров", а как "Ильф и его соавтор". Пишем следующий запрос на MySQL:

SELECT INSERT (Author, 8, 6, 'его соавтор') AS New_Author FROM Bookinuse WHERE Title='Двенадцать стульев'

Результат выполнения запроса:

New_Author
Ильф и его соавтор

Пишем запрос на MS SQL Server (с предваряющей конструкцией USE library;):

SELECT STUFF (Author, 8, 6, 'его соавтор') AS New_Author FROM Bookinuse WHERE Title='Двенадцать стульев'

Результат будет аналогичным.

Функция SQL REPLACE()

Эта функция возвращает новую строку, являющуюся результатом того, что в исходной строке str все подстроки substr1 заменены на подстроки substr2. Написание функции:
REPLACE(str, substr1, substr2).

Пример 3. Викторина в библиотеке продолжается. Требуется заменить фамилию автора "Чехов" на "Мастер короткого рассказа". Пишем следующий запрос на MySQL:

SELECT REPLACE (Author, 'Чехов', 'Мастер короткого рассказа') FROM Bookinuse

Так как запрос написан без секции WHERE, замена произойдёт во всех записях, соответствующих записям исходной таблицы. Результат выполнения запроса:

REPLACE (Author, 'Чехов' 'Мастер короткого рассказа')
Толстой
Мастер короткого рассказа
Мастер короткого рассказа
Мастер короткого рассказа
Ильф и Петров
Маяковский
Пастернак
Толстой
Толстой
Пушкин
Гоголь
Мастер короткого рассказа
Пастернак

Функция SQL LOWER()

Эта функция возвращает новую строку str, в которой все символы исходной строки переведены в нижний регистр. Написание функции:
LOWER(str).

Пример 4. Пусть требуется написать фамилии всех авторов в нижнем регистре. Пишем следующий запрос:

SELECT LOWER(Author) FROM Bookinuse

Начало и конец таблицы, являющейся результатом выполнения этого запроса будет выглядеть так:

LOWER(Author)
толстой
чехов
чехов
чехов
...
гоголь
чехов
пастернак

Функция SQL UPPER()

Эта функция возвращает новую строку str, в которой все символы исходной строки переведены в верхний регистр. Написание функции:
UPPER(str).

Пример 5. Пусть требуется написать фамилии всех авторов в верхнем регистре. Пишем следующий запрос:

SELECT UPPER(Author) FROM Bookinuse

Начало и конец таблицы, являющейся результатом выполнения этого запроса будет выглядеть так:

LOWER(Author)
ТОЛСТОЙ
ЧЕХОВ
ЧЕХОВ
ЧЕХОВ
...
ГОГОЛЬ
ЧЕХОВ
ПАСТЕРНАК

Примеры запросов к базе данных "Библиотека" есть также в уроках по операторам GROUP BY, IN, предикату EXISTS и функции COALESCE.

Функция MySQL LENGTH() и функция MS SQL Server LEN()

Названия этих функций различаются в разных СУБД, но выполняют они одну задачу: возвращают длину строки str. Написание функции:
LENGTH(str);
LEN(str).

Пример 6. Пусть требуется узнать длину фамилий авторов выданных книг. Пишем следующий запрос:

SELECT Author, LENGTH(Author) FROM BOOKINUSE

Начало и конец таблицы, являющейся результатом выполнения этого запроса будет выглядеть так:

AuthorLENGTH(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.

Поделиться с друзьями

Реляционные базы данных и язык SQL