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

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

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

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

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

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

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

AuthorTitlePubyearInv_NoUser_ID
ТолстойВойна и мир20052865
ЧеховВишневый сад20001731
ЧеховИзбранные рассказы201119120
ЧеховВишневый сад1991565
Ильф и ПетровДвенадцать стульев1985331
МаяковскийПоэмы19832120
ПастернакДоктор Живаго200669120
ТолстойВоскресенье20067747
ТолстойАнна Каренина19897205
ПушкинКапитанская дочка20042547
ГогольПьесы20078147
ЧеховИзбранные рассказы19874205
ПастернакИзбранное200013718

Функция SQL CONCAT()

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

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

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

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

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

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

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

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

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

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

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

Функция 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)
ТОЛСТОЙ
ЧЕХОВ
ЧЕХОВ
ЧЕХОВ
...
ГОГОЛЬ
ЧЕХОВ
ПАСТЕРНАК

Функция 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