16.10.25

Способы объединения значений в символьные строки + что привнёс SQL Server 2025

Автор: Louis Davidson, Concatenating values as character data including in SQL Server 2025

Недавно я искал тему для новой заметки и перечитал «Новые возможности SQL Server 2025 (предварительная версия)». Нашёл вот это:

|| (String concatenation) Concatenate expressions with expression || expression.

Мой интерес, мягко говоря, проснулся. Использование + для сцепления строк всегда имело свои проблемы, и, как бы мне ни нравилась функция CONCAT, она немного тяжеловесна по сравнению с полноценным оператором.

Существует также функция CONCAT_WS, которая добавляет разделитель в результат, но я не буду о ней говорить в этой статье.

Я пойду к новой возможности «обходными путями», но если хотите пропустить, просто переходите к разделу, где она разобрана.

Предпосылки

Вероятно, вторая задача любого программиста — сразу после вывода 'Hello World' — это изменить вывод, добавив ещё какое‑то значение. В T‑SQL это может выглядеть так:

DECLARE @variable varchar
SET @variable = 'Bob'
SELECT 'Hello' + @variable

Конечно, дальше мы исправляем то, что получили HelloBob без пробела, но если вспомнить первые шаги в программировании, сам факт объявления переменной уже был захватывающим. В T‑SQL мы все делали что‑то вроде такого:

SELECT 'Start-' + 'Middle' + '-End';

Для простоты в примерах я буду использовать литерал посередине, но обычно форматируем данные из столбца таблицы. Это выдаёт:

----------------
Start-Middle-End

Что, как правило, «на чистых данных» отлично работает в тестах.

Значения NULL

Но затем, как это и бывает в реальных данных, появляются значения NULL:

SELECT 'Start-' + NULL + '-End';

И — о чудо — на выходе получаем NULL:

----------------
NULL

Для этого есть вполне разумная причина, которая лучше «ложится» на математику: NULL означает «неизвестно», и 1 + неизвестно действительно нельзя вычислить. Но 'Start' + любое значение, известное или неизвестное, в типичных случаях «должно» начинаться с 'Start'. (Наверняка есть случаи, когда это не так — я не настолько умен, чтобы заседать в комитете, который решает такие вещи!)

Числа

Даже если забыть про NULL, есть и другие вопросы — например, в следующем запросе мы надеемся получить Start-10-End:

SELECT 'Start-' + 10 + '-End';

Но вместо этого получаем ошибку:

-----------
Msg 245, Level 16, State 1, Line 17
Conversion failed when converting the varchar value 'Start-' to data type int.

Дата и время

Кажется, что с датами должно быть проще. Когда мы создаём значение даты, ведь используем строковый литерал, верно?

SELECT 'Start-' + SYSDATETIME() + '-End';

Увы, даты тоже не работают:

Msg 402, Level 16, State 1, Line 23
The data types varchar and datetime2 are incompatible in the add operator.

Обратите внимание на небольшое отличие: здесь даже заголовок столбца не выводится — ошибка возникает раньше, чем в случае с целым.

Uniqueidentifier (GUID)

На этом этапе вы уже видите закономерность, но всё же:

SELECT 'Start-' + NEWID() + '-End';

И это тоже не даст желаемого результата, хотя, как и у дат, у GUID есть очевидное строковое представление:

Msg 402, Level 16, State 1, Line 30
The data types varchar and uniqueidentifier are incompatible in the add operator.

Существующие приёмы

В SQL Server есть много способов справляться с подобным — в зависимости от ситуации. Например, можно использовать функции ISNULL, COALESCE, CAST или CONVERT:

SELECT 'Start-' + ISNULL(NULL,'') + '-End',
       'Start-' + CAST(10 as varchar(10)) + '-End';

Это даёт желаемый результат: первая колонка «поглощает» NULL, во второй корректно выводится 10:

---------------- -------------------------
ValueOtherValue  Value10OtherValue

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

Конечно, в SQL Server 2012 (уже очень давно) Microsoft добавила функцию CONCAT. Она решает «простые» случаи вроде такого:

SELECT CONCAT('Start-', NULL,  '-End'),
       CONCAT('Start-', 10,    '-End');

Что возвращает:

---------- ----------------------
Start--End Start-10-End

CONCAT трактует NULL как пустую строку. Это отлично подходит для данных, отсутствие которых допустимо (например, отчество). Но может быть запутанно, если вы не ожидали там NULL — функция не выдаёт даже предупреждения.

Повторим примеры изначально:

SELECT CONCAT('Start-', NEWID(),      '-End'),
       CONCAT('Start-', SYSDATETIME(),'-End');

Это возвращает строку с внедрённым GUID в первой колонке и очень детализированным временем во второй:

--Note: one row of output, broken into two:
--------------------------------------------------
Start-19788F6E-D2D4-4BE2-8710-2A7A03C5C5BC-End
--------------------------------------------------
Start-2025-10-13 23:07:24.9661700-End

Оператор ||

Но можно ли лучше? Конечно. В SQL Server 2025 добавлен новый оператор конкатенации. Подобно функции CONCAT, это стандарт SQL. Оператор конкатенации — ||, и он замечателен тем, что так же прост, как +, но при этом по‑другому обрабатывает типы — а вот с NULL всё «как в стандарте».

Оператор работает со строковыми и двоичными данными. Как и в большинстве строковых операций SQL Server, если не использовать типы с суффиксом (max) (varchar(max), nvarchar(max) и т. д.), результат ограничен 8000 байт. Он очень похож на CONCAT, за одним существенным исключением — обработкой NULL. К счастью, он «приводит» типы, для которых есть очевидное строковое представление.

Примеры:

SELECT 'Start-' || 10 || '-End';

Результат:

----------------------
Start-10-End


Обратите внимание: хотя бы один из операндов должен быть строкой. Например, SELECT 10 || '10'; вернёт varchar со значением 1010. Но SELECT 10 || 10; вернёт ошибку: Msg 402, Level 16, State 1, Line 129 / The data types int and int are incompatible in the concat operator.

Смотрим теперь на GUID:

SELECT 'Start-' || newid() || '-End';

Результат:

--------------------------------------------------
Start-EC6ACA8C-B311-47C2-8CD4-B0C079545AEC-End

С датой:

SELECT 'Start-' || sysdatetime() || '-End';

Результат:

--------------------------------------------------
Start-2025-10-12 22:59:03.6890156-End

Понятно, что часто вы захотите отформатировать такие значения иначе, но и такой вывод порой бывает полезен.

Если же в выражении встречается NULL:

SELECT 'Start-' || NULL || '-End';

Получаем NULL:

-----------
NULL

Это потому, что как стандартный оператор он следует базовой аксиоме: NULL +,/,=,* и т. п. всегда дают NULL. Здорово это или нет — зависит от ваших целей. Чаще всего, когда я перехожу с + на CONCAT, мне как раз нужно «спрятать» NULL.

Будучи стандартной возможностью SQL, оператор не учитывает настройку SET CONCAT_NULL_YIELDS_NULL, поэтому любой NULL в выражении приведёт к NULL‑результату.

Один из моих любимых трюков — совмещать + и CONCAT при форматировании ФИО. В данном случае это будет работать и с ||, и с +:

DECLARE @FirstName  nvarchar(50) = 'First',
        @MiddleName nvarchar(50),
        @LastName   nvarchar(50) = 'Last' -- Обычно NOT NULL как столбец
SELECT CONCAT(@FirstName + ' ', @MiddleName + ' ', @LastName);
SELECT CONCAT(@FirstName || ' ', @MiddleName || ' ', @LastName);

Оба запроса дают одинаковый результат, потому что NULL в @MiddleName, сцепляясь с ' ' внутри CONCAT, превращается в '', а не в NULL:

---------------
First Last

Только один пробел: @MiddleName (или NULL) + ' ' даёт NULL, и CONCAT это игнорирует.

Сокращённое присваивание

Есть и «сократительный» способ дописать значение в конец строки. Он работает аналогично += для числовых (и строковых) данных, но записывается как ||=. (Честно признаюсь, я сам редко пользовался += и предпочитал более «многословные» формы, так что если вы как я — вот короткий пример.)

Прибавить 1 к переменной:

DECLARE @Variable int = 0;
SET     @Variable += 1;
SELECT  @Variable;

Результат:

-----------
1

По сути, это сокращение для «возьми правый операнд, прибавь к @Variable и запиши обратно в переменную».

Работает и со строками:

DECLARE @Variable varchar(20) = 'a';
SET     @Variable += 'b';
SELECT  @Variable;

Результат:

--------------------
ab

Новый вариант делает то же самое, но включает поддержку прочих типов (как ||):

DECLARE @Variable varchar(20) = 'a';
SET     @Variable ||= 'b';
SELECT  @Variable;
SET     @Variable ||= sysdatetime() -- обрежется по длине переменной
SELECT  @Variable;

Два значения в выводе:

--------------------
ab
--------------------
ab2025-10-12 23:12:1

Короткий пример с бинарными данными

В основном это про строки, но не только. Можно объединять и двоичные данные. Например:

DECLARE @Variable varbinary(100)
-- приводим значение к бинарному виду
SET @Variable = cast('Hello' as varbinary(100))
-- далее сцепляем ещё одно бинарное значение, соответствующее ' Hello'
SET @Variable = @Variable || cast(' Hello' as varbinary(100))
-- приведём вывод, чтобы увидеть результат:
SELECT @variable;                     -- шестнадцатеричный вид
SELECT cast(@Variable as varchar(30)); -- текстовая версия

Две строки вывода:

---------------------------
0x48656C6C6F2048656C6C6F
------------------------------
Hello Hello

Оговорка: нельзя сцеплять небинарные значения «как есть» — нужно приводить типы. Если попытаться:

DECLARE @Variable varbinary(100);
SET     @Variable = cast('Hello' as varbinary(100));
SET     @Variable = @Variable || 'Test'; -- сцепить строковый литерал 'Test'

В отличие от строк, это даст ошибку:

Msg 402, Level 16, State 1, Line 156
The data types varbinary and varchar are incompatible in the concat operator.

Итоги

В SQL Server очень много способов работать со строковыми данными, и в SQL Server 2025 добавился ещё один — новый (для SQL Server) оператор конкатенации ||. Это интересный оператор, который по своим свойствам при сцеплении строк оказывается «между» + и CONCAT, если смотреть именно на обработку NULL. Так что когда вам нужно, чтобы NULL приводил к NULL и при этом хочется иметь возможность сцеплять вместе значения разных типов — это отличный выбор.

Например, когда требуется форматировать число с префиксом. Можно просто написать:

'Prefix-' || NumericValue

И результат будет NULL, если NumericValueNULL, и Prefix-Number, если нет. Очень удобно.

Комментариев нет:

Отправить комментарий