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

Навигация по уроку

Функция SQL CONCAT()
Функция MySQL INSERT() и функция MS SQL Server STUFF()
Функция SQL REPLACE()
Функция SQL LOWER()
Функция SQL UPPER()
Функция MySQL LENGTH() и функция MS SQL Server LEN()
Ещё несколько употребительных функций SQL для работы со строками

Связанные темы

Оператор SELECT

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

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

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

Если использовать запрос без необязательной секции 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 указываем пробел ' '. Пишем следующий запрос:

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

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

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.

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

Назад<<<

Содержание

Вперёд>>>