There are several ways to concatenate strings in SQL, and they mainly differ in how they handle NULL values.
Simple concatenation
Strings can be concatenates with the addition operator (+). For example:
SELECT 'Hello ' + 'world!' AS greetings;
SELECT (name + ' ' + surname) AS who FROM customer;If one of the values is NULL, by default the + operator will return NULL. This behaviour can be changed in two ways:
- At session level:
SET CONCAT_NULL_YIELDS_NULL OFF - At database level:
ALTER DATABASE db_name SET CONCAT_NULL_YIELDS_NULL OFF.
In both cases, disabling CONCAT_NULL_YIELDS_NULL will cause NULL values to be ignored. For example:
SELECT (name + surname) AS who FROM customer;If name='Sofia' and surname is NULL, the return value will be 'Sofia'.
CONCAT()
The CONCAT() function returns a concatenation of all the arguments.
SELECT CONCAT(name, ' ', surname) AS who FROM customer;If any argument is NULL, an empty string of type VARCHAR(1) is returned. CONCAT() never returns NULL and never ignores NULL arguments.
CONCAT_WS()
CONCAT_WS() stands for concatenate with separator. It returns all the arguments after the first, using the first argument as a separator. For example:
SELECT CONCAT_WS(' ', name, middle_name, surname) AS who FROM customer;This will return something like 'James Tiberius Kirk', in other words it will return the three mentioned columns separated by a space. The separator can consist of multiple characters as well.
If any argument is NULL, an empty string of type VARCHAR(1) is returned. CONCAT_WS() never returns NULL and never ignores NULL arguments. CONCAT() and CONCAT_WS() are the same in this respect.
Return types
When all arguments are of the same type, a result of the same type is returned. When arguments are of different types, the type with highest precedence will be used for the returned value. See Data type precedence, in the SQL Server documentation.