SUBSTRING: примеры (SQL)
SUBSTRING(expression, start, length): stringОписание функции SUBSTRING
Функция SUBSTRING в MS SQL Server предназначена для извлечения части строки или сегмента данных из текстового, бинарного или символьного столбца. Она часто используется в задачах обработки строк, таких как парсинг данных, извлечение подстрок по определенным позициям или подготовка значений для отчетов.
Функция принимает три обязательных аргумента:
expression – исходное выражение, которое может быть строкового типа (char, varchar, nchar, nvarchar, text, ntext) или бинарного (binary, varbinary, image).
start – целочисленная позиция символа, с которого начинается извлечение. Отсчет начинается с 1. Если значение отрицательное или ноль, функция вернет часть строки, начиная с первого символа.
length – количество символов, которые необходимо извлечь. Если указанное значение превышает количество символов, доступных от начальной позиции до конца строки, функция вернет все символы до конца строки.
Возвращаемое значение соответствует типу исходного выражения, за исключением типов text, ntext и image, для которых возвращается varchar, nvarchar и varbinary соответственно.
Простые примеры использования
Извлечение подстроки с указанием позиции и длины:
SELECT SUBSTRING('Microsoft SQL Server', 11, 3) AS Result;Result --- SQL
Использование отрицательного значения для начальной позиции:
SELECT SUBSTRING('Пример строки', -2, 5) AS Result;Result --- Прим
Указание длины, превышающей доступное количество символов:
SELECT SUBSTRING('Тест', 2, 10) AS Result;Result --- ест
Работа с бинарными данными:
SELECT SUBSTRING(0x53616D706C65, 2, 3) AS Result;Result --- 0x616D70
Похожие функции в T-SQL
LEFT и RIGHT извлекают указанное количество символов с начала или конца строки соответственно. Эти функции удобнее, когда нужно получить префикс или суффикс строки.
CHARINDEX и PATINDEX находят позицию подстроки или шаблона в строке. Часто используются вместе с SUBSTRING для динамического определения позиции начала извлечения.
Функция STUFF удаляет часть строки и вставляет другую строку начиная с заданной позиции. Может применяться для замены фрагментов.
Выбор функции зависит от конкретной задачи: для извлечения фиксированных частей по известным позициям подходит SUBSTRING, для работы с начала или конца строки – LEFT/RIGHT, для поиска позиций – CHARINDEX/PATINDEX.
Аналоги в других СУБД и языках
MySQL: Используется функция SUBSTRING или её синоним SUBSTR. Нумерация символов начинается с 1. Допускается отрицательное значение для начала отсчета с конца строки.
SELECT SUBSTRING('MySQL Database', 7, 4);Database
PostgreSQL: Функция SUBSTR работает аналогично, но также поддерживается извлечение по шаблону с помощью SUBSTRING с регулярными выражениями.
SELECT SUBSTR('PostgreSQL', 6, 3);SQL
Oracle: Основной функцией является SUBSTR. Позиция может быть отрицательной для отсчета с конца строки.
SELECT SUBSTR('Oracle DB', 1, 6) FROM dual;Oracle
SQLite: Используется функция SUBSTR с аналогичным синтаксисом.
SELECT substr('SQLite', 4, 3);ite
В отличие от MS SQL, в некоторых СУБД (например, MySQL) функция допускает более гибкие варианты синтаксиса, а в PostgreSQL есть расширенная версия для работы с регулярными выражениями.
Типичные ошибки
Передача недопустимых типов данных в качестве аргументов:
SELECT SUBSTRING(12345, 2, 3); -- Ошибка, если тип не строковыйПриведение типа не поддерживается
Некорректная интерпретация позиций из-за наличия многобайтовых символов (Unicode):
SELECT SUBSTRING(N'Тест', 2, 2); -- Для nvarcharес
Использование функции для типов данных, которые были объявлены устаревшими:
SELECT SUBSTRING(CAST('Old' AS text), 1, 2); -- Работает, но text устарелOl
Забывают, что длина может быть опущена только в других СУБД, а в T-SQL она обязательна:
SELECT SUBSTRING('Test', 2); -- Ошибка в MS SQLНеверное количество аргументов
Изменения в последних версиях
В последних версиях MS SQL Server синтаксис функции SUBSTRING остался неизменным. Основные изменения связаны с поддержкой новых типов данных и улучшением производительности при обработке больших строк.
В SQL Server 2012 и выше повышена эффективность работы функции с типами данных, которые используют сжатие Unicode (SC).
Для устаревших типов text, ntext и image рекомендуется использовать типы varchar(max), nvarchar(max) и varbinary(max), с которыми SUBSTRING работает без ограничений.
Расширенные примеры
Динамическое извлечение части строки между двумя разделителями:
DECLARE @str VARCHAR(100) = 'Имя: Иван; Фамилия: Петров;';
SELECT SUBSTRING(@str, CHARINDEX(':', @str) + 2, CHARINDEX(';', @str) - CHARINDEX(':', @str) - 2) AS NamePart;NamePart --- Иван
Использование в сочетании с другими строковыми функциями для форматирования:
SELECT
SUBSTRING(REPLACE('+7(123)456-78-90', '-', ''), 3, 3) AS Code;Code --- 123
Рекурсивное разбиение строки с помощью SUBSTRING в цикле:
DECLARE @data VARCHAR(50) = 'A,B,C,D,E', @pos INT = 1;
WHILE @pos > 0
BEGIN
SET @pos = CHARINDEX(',', @data);
IF @pos > 0
SELECT SUBSTRING(@data, 1, @pos-1) AS Part;
SET @data = SUBSTRING(@data, @pos+1, LEN(@data));
ENDPart --- A B C D E
Извлечение данных из JSON строки до появления специальных функций JSON:
SELECT SUBSTRING('{"id": 123, "name": "test"}',
CHARINDEX(':', '{"id": 123, "name": "test"}') + 2,
CHARINDEX(',', '{"id": 123, "name": "test"}') - CHARINDEX(':', '{"id": 123, "name": "test"}') - 3) AS ID_Value;ID_Value --- 123
Применение в UPDATE для модификации части значения столбца:
-- Создание тестовой таблицы
CREATE TABLE #Temp (Code VARCHAR(10));
INSERT INTO #Temp VALUES ('ABC-123'), ('XYZ-789');
UPDATE #Temp SET Code = SUBSTRING(Code, 5, 3) + '-' + SUBSTRING(Code, 1, 3);
SELECT * FROM #Temp;Code --- 123-ABC 789-XYZ