Рисунок 1. Результат сложения столбцов со значением NULL

Склеивание значений столбцов – не самая тривиальная задача в T-SQL, если вы не знакомы со стандартом ANSI. Согласно ему, при сложении хотя бы одного составляющего со значением NULL результат будет также NULL. Об этом знают администраторы баз данных, но не факт, что известно обычным разработчикам.

Рассмотрим несколько особенностей склеивания столбцов с из таблицы MS SQL Server. Для примера будем использовать бесплатную учебную базу данных Northwind и таблицу Customers.

Запустим SQL-запрос и посмотрим на результат (Таблица 1):

 select Address, City, Region, PostalCode, Country from Customers

Таблица 1. Результат SQL запроса

Address City Region PostalCode Country
Obere Str. 57 Berlin NULL 12209 Germany
Avda. de la Constitución 2222 México D.F. NULL 5021 Mexico
Mataderos  2312 México D.F. NULL 5023 Mexico
120 Hanover Sq. London NULL WA1 1DP UK
Berguvsvägen  8 Luleå NULL S-958 22 Sweden
Forsterstr. 57 Mannheim NULL 68306 Germany
24, place Kléber Strasbourg NULL 67000 France

 

В таблице видно, что некоторые значения столбца Region имеют значение NULL.

Склеим строки так, чтобы полный адрес был единственным значением:

select Country + ', ' +  City + ', ' + Region + ', ' + PostalCode + ', ' + Address from Customers

Результат будет отличен от того, каким его ожидает увидеть разработчик. Логично было б увидеть что-то типа "Germany, Berlin, , 12209, Obere Str. 57", но результатом будет много значений NULL  в строках (Рисунок 1):

Рисунок 1. Результат сложения столбцов со значением NULL

Рисунок 1. Результат сложения столбцов со значением NULL

Можно интерпретировать значение NULL как пустую строку с помощью параметра CONCAT_NULL_YIELDS_NULL. По умолчанию его значение ON, но можно его поменять для текущей сессии на OFF. Тогда результат будет больше похож на то, что нужно.

SET CONCAT_NULL_YIELDS_NULL OFF
select Country + ', ' +  City + ', ' + Region + ', ' + PostalCode + ', ' + Address from Customers

 

Рисунок 2. Сложение строк после замены значения параметра CONCAT_NULL_YIELDS_NULL на OFF

Рисунок 2. Сложение строк после замены значения параметра CONCAT_NULL_YIELDS_NULL на OFF

 

Не забудьте снова вернуть значение параметра CONCAT_NULL_YIELDS_NULL на ON (SET CONCAT_NULL_YIELDS_NULL ON)

 

Другой способ – воспользоваться функцией COALESCE (expression, value_instead_of_null), которая возвращает значение "value_instead_of_null", если выражение есть NULL. Запрос для склеивани строк будет таким:

select COALESCE(Country, '') + ', ' +  COALESCE(City, '') + ', ' + COALESCE(Region, '') + ', ' + COALESCE(PostalCode, '') + ', ' + COALESCE(Address, '') from Customers

Результат SQL-запроса представлен на Рисунке 2

 

В версии MS SQL Server 2012 появилась функция CONCAT (expr1, expr2, expr3, … , exprN) для склеивания строчек.

select CONCAT(Country, ', ', City, ', ', Region, ', ', PostalCode,', ', Address ) from Customers

 

Результат этого SQL-запроса такой же, как и на Рисунке 2.

 

Строчки склеиваются, несмотря на значения NULL. Но для финальной красоты остается убрать двойные запятые ", ,". Это можно сделать с помощью функции REPLACE:

select REPLACE( COALESCE(Country, '') + ', ' +  COALESCE(City, '') + 
			', ' + COALESCE(Region, '') + ', ' + COALESCE(PostalCode, '') +
			', ' + COALESCE(Address, '') , ', ,', ',') 
			from Customers

 

Рисунок 3. Полный адрес одним значением

Рисунок 3. Полный адрес одним значением