Недавно я искал тему для новой заметки и перечитал «Новые возможности 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
, если NumericValue
— NULL
, и Prefix-Number
, если нет. Очень удобно.
Комментариев нет:
Отправить комментарий