Использование функций для работы со строками в SQL: полное руководство

Введение в строковые функции SQL Server

Строковые функции в MS SQL Server — это встроенные инструменты, предназначенные для обработки текстовых данных. Они позволяют выполнять такие операции, как извлечение подстрок, поиск вхождений, замена символов, изменение регистра, удаление пробелов и объединение строк. Грамотное применение этих функций избавляет от необходимости писать громоздкие циклы на клиентской стороне и значительно повышает производительность запросов.

В арсенале SQL Server насчитывается более двух десятков строковых функций. Среди них есть как простые (LEN, UPPER), так и более сложные (PATINDEX, STUFF, STRING_AGG). Выбор конкретной функции зависит от версии SQL Server и поставленной задачи. Например, CONCAT и STRING_AGG доступны начиная с SQL Server 2012 и 2017 соответственно, а для более старых версий приходится использовать альтернативные приёмы, такие как FOR XML PATH.

Понимание поведения функций с NULL-значениями — ключевой момент. Оператор + возвращает NULL, если хотя бы один операнд равен NULL, тогда как CONCAT автоматически преобразует NULL в пустую строку. Аналогично, LEN не учитывает конечные пробелы, что может привести к неожиданным результатам при проверке длины.

Функции для определения длины и извлечения подстрок

LEN возвращает количество символов в строке, исключая конечные пробелы. Например, LEN('Привет, мир!') вернёт 12. Если нужно учесть все пробелы, следует использовать DATALENGTH для типов данных фиксированной длины.

LEFT и RIGHT извлекают заданное количество символов соответственно с начала или с конца строки. LEFT('Привет, мир!', 7) даст 'Привет,', а RIGHT('Привет, мир!', 5) — 'мир!'. Важно помнить, что если запрашиваемая длина превышает длину строки, функция вернёт всю строку без ошибки.

SUBSTRING — наиболее гибкая функция для извлечения подстроки по начальной позиции и длине. SUBSTRING('abcdef', 2, 3) вернёт 'bcd'. Если длина не указана или превышает остаток строки, возвращается всё до конца. SUBSTRING часто используется совместно с CHARINDEX для динамического определения позиции.

Поиск подстроки: CHARINDEX и PATINDEX

CHARINDEX ищет точное вхождение подстроки и возвращает начальную позицию (нумерация с 1). Если подстрока не найдена, результат — 0. Пример: CHARINDEX('world', 'Hello world') вернёт 7. По умолчанию поиск учитывает регистр, если не задана соответствующая сортировка (COLLATE).

PATINDEX работает аналогично, но поддерживает шаблоны с символами % (любая последовательность) и _ (один символ). Например, PATINDEX('%[0-9]%', 'abc123def') вернёт 4 — позицию первой цифры. PATINDEX всегда учитывает регистр, если сортировка не указана явно.

Обе функции полезны для парсинга строк, извлечения данных из неструктурированного текста и проверки форматов. Например, с их помощью можно найти email-адрес или номер телефона в произвольном тексте.

Изменение регистра и удаление пробелов

UPPER и LOWER преобразуют все символы строки в верхний или нижний регистр соответственно. UPPER('hello') вернёт 'HELLO', а LOWER('WORLD') — 'world'. Эти функции незаменимы при регистронезависимом сравнении или форматировании вывода.

LTRIM и RTRIM удаляют пробелы в начале и конце строки. Начиная с SQL Server 2017, появилась функция TRIM, которая удаляет пробелы с обеих сторон за один вызов. До этой версии приходилось комбинировать LTRIM и RTRIM: LTRIM(RTRIM(' текст ')).

Важно: LEN не учитывает конечные пробелы, поэтому после RTRIM длина может не измениться. Для точного подсчёта символов с пробелами используйте DATALENGTH.

Замена и модификация строк: REPLACE, STUFF, TRANSLATE

REPLACE заменяет все вхождения одной подстроки на другую. REPLACE('Microsoft SQL Server', 'SQL', 'MySQL') вернёт 'Microsoft MySQL Server'. Функция чувствительна к регистру в зависимости от сортировки базы данных.

STUFF позволяет удалить указанное количество символов, начиная с заданной позиции, и вставить на их место другую строку. STUFF('abcdef', 2, 2, 'XYZ') даст 'aXYZef'. STUFF часто применяется в связке с FOR XML PATH для агрегации строк (см. следующий раздел).

TRANSLATE (доступна с SQL Server 2017) заменяет каждый символ из второго аргумента на соответствующий символ из третьего. Например, TRANSLATE('2*[3+4]/(7-2)', '[]()', '{}()') заменит [ на {, ] на }, а скобки останутся без изменений. Это удобно для экранирования или замены нескольких символов одним вызовом.

Объединение строк: CONCAT, CONCAT_WS и оператор +

CONCAT (SQL Server 2012+) принимает от 2 до 254 аргументов и объединяет их в одну строку, автоматически преобразуя NULL в пустую строку. CONCAT('Привет', ' ', NULL, 'мир') вернёт 'Привет мир'. Это безопасная альтернатива оператору +.

CONCAT_WS (SQL Server 2017+) добавляет разделитель между элементами, игнорируя NULL. CONCAT_WS(', ', 'Иванов', NULL, 'Петров') даст 'Иванов, Петров'.

Оператор + работает быстрее, но требует явной обработки NULL с помощью ISNULL или COALESCE. ISNULL(FirstName, '') + ' ' + ISNULL(LastName, '') — типичный пример для старых версий. Ошибка: забыть обработать NULL — тогда весь результат станет NULL.

Агрегация строк: STRING_AGG и FOR XML PATH

STRING_AGG (SQL Server 2017+) объединяет значения из разных строк в одну строку с указанным разделителем. SELECT STRING_AGG(LastName, ', ') FROM Employees вернёт 'Иванов, Петров, Сидоров'. Функция поддерживает сортировку через WITHIN GROUP (ORDER BY ...).

До появления STRING_AGG использовался приём с FOR XML PATH и STUFF. Пример:

SELECT STUFF(
  (SELECT ', ' + LastName FROM Employees ORDER BY LastName FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
  1, 2, '') AS AllNames;

Этот метод работает во всех версиях, но требует осторожности: XML-экранирование спецсимволов (<, >, &) может исказить результат. Использование TYPE и .value() помогает избежать этой проблемы.

Для обратной совместимости (SQL Server 2008–2016) FOR XML PATH остаётся основным решением, хотя он менее производителен и сложнее в написании.

Разбиение строки на части: STRING_SPLIT и рекурсивные CTE

STRING_SPLIT (SQL Server 2016+) разбивает строку по разделителю и возвращает таблицу с одним столбцом value. SELECT value FROM STRING_SPLIT('apple,banana,cherry', ',') вернёт три строки. Функция не гарантирует порядок вывода, поэтому при необходимости сортировки нужно добавлять ORDER BY.

Для версий до 2016 года можно использовать рекурсивное CTE (Common Table Expression). Пример:

DECLARE @data NVARCHAR(MAX) = 'apple,banana,cherry';
DECLARE @delimiter CHAR(1) = ',';
WITH SplitCTE AS (
  SELECT LEFT(@data, CHARINDEX(@delimiter, @data + @delimiter) - 1) AS Value,
         RIGHT(@data, LEN(@data) - CHARINDEX(@delimiter, @data + @delimiter)) AS Remaining
  UNION ALL
  SELECT LEFT(Remaining, CHARINDEX(@delimiter, Remaining + @delimiter) - 1),
         RIGHT(Remaining, LEN(Remaining) - CHARINDEX(@delimiter, Remaining + @delimiter))
  FROM SplitCTE WHERE LEN(Remaining) > 0
)
SELECT Value FROM SplitCTE;

Этот метод работает в любой версии, но может быть медленным на больших строках.

Дополнительные функции: ASCII, UNICODE, SOUNDEX, DIFFERENCE

ASCII возвращает код первого символа строки в кодировке ASCII. ASCII('A') вернёт 65. CHAR выполняет обратное преобразование — код в символ. Эти функции полезны для низкоуровневой обработки текста.

UNICODE и NCHAR работают аналогично, но для Unicode-символов. UNICODE(N'А') вернёт 1040, а NCHAR(1040) — 'А'.

SOUNDEX возвращает четырёхсимвольный код, представляющий звучание строки на английском языке. DIFFERENCE сравнивает два SOUNDEX-кода и возвращает число от 0 до 4 (4 — наилучшее совпадение). Эти функции могут использоваться для поиска похожих по звучанию имён, но они ориентированы на английскую фонетику и не подходят для русского языка.

REPLICATE повторяет строку заданное количество раз. REPLICATE('0', 5) вернёт '00000'. SPACE генерирует строку из указанного числа пробелов. STR преобразует число в строку с заданной длиной и количеством десятичных знаков.

Типичные ошибки и рекомендации

1. NULL-значения: Использование оператора + без обработки NULL приводит к NULL-результату. Всегда применяйте ISNULL или COALESCE, либо переходите на CONCAT.
2. Конечные пробелы: LEN не учитывает пробелы в конце. Для точной длины используйте DATALENGTH для char/nchar или LEN(REPLACE(str, ' ', '.')).
3. Регистрозависимость: CHARINDEX и PATINDEX по умолчанию учитывают регистр. Если нужно регистронезависимое сравнение, укажите COLLATE, например CHARINDEX('sql', 'SQL Server' COLLATE Latin1_General_CI_AS).
4. XML-экранирование: При использовании FOR XML PATH символы <, >, & экранируются. Используйте TYPE и .value() для получения чистого текста.
5. Производительность: Избегайте вложенных вызовов строковых функций в WHERE-условиях — это может привести к сканированию таблицы. По возможности используйте вычисляемые столбцы или полнотекстовый поиск.

Рекомендуется всегда проверять версию SQL Server и выбирать наиболее современную функцию (CONCAT вместо +, STRING_AGG вместо FOR XML PATH, TRIM вместо LTRIM+RTRIM). Это упрощает код и повышает его читаемость.

Вопросы и ответы