+ Operator to Concatenate Strings - SQL Server

+ operator concatenates one or more strings into a single string in Microsoft SQL Server and SQL Azure.

Quick Example:

   -- Concatenate strings 'New ' and 'York'
   SELECT 'New ' + 'York';
   -- Result: New York

Summary information:

Syntax	string_expression1 + string_expression2 + …
NULL Values	If any value in NULL, the result is NULL unless SET CONCAT_NULL_YIELDS_NULL OFF
Expression Conversion	Number and datetime expressions must be implicitly converted to string before concatenation
Alternatives	CONCAT function	NULL is '' (empty string)	Available since SQL Server 2012 only

+ operator concatenates one or more string expressions into a single string.

   SELECT 'The city' + ' is ' + 'Paris';
   -- Result: The city is Paris

If any expression is NULL, + operator returns NULL for the entire expression (by default):

   SELECT 'The city' + ' is ' + NULL;
   -- Result: NULL

But by setting CONCAT_NULL_YIELDS_NULL to OFF, you can specify to treat NULL as '' (empty string) by + operator:

   SET CONCAT_NULL_YIELDS_NULL OFF
 
   SELECT 'The city' + ' is ' + NULL;
   -- Result: The city is

Microsoft does not recommend using SET CONCAT_NULL_YIELDS_NULL OFF, and plans to always set this option to ON, so you should use CONCAT function or ISNULL function for individual expressions to replace NULL with '' (empty string):

   SELECT CONCAT('The city', ' is ', NULL);
   -- Result: The city is
 
   SELECT 'The city' + ' is ' + ISNULL(NULL, '');
   -- Result: The city is

+ operator requires explicit conversion of numbers and datetime values to string before concatenation:

  SELECT 1 + 'st';
  -- Error:
  -- Msg 245, Level 16, State 1, Line 1
  -- Conversion failed when converting the varchar value 'st' to data type int.
 
  SELECT 'Today is ' + GETDATE();
  -- Error:
  -- Msg 241, Level 16, State 1, Line 1
  -- Conversion failed when converting date and/or time from character string.

You can use CONCAT function that implicitly converts numbers and datetime values to string before concatenation:

  SELECT CONCAT(1, 'st');
  -- Result: 1st
 
  SELECT CONCAT('Today is ', GETDATE());
  -- Result: Today is May 28 2012  6:13PM

For more information, see SQL Server Migration.