+ 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

Last Update: Microsoft SQL Server 2012

+ 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

• Oracle to SQL Server Migration
• SQL Server Articles
• Converting Oracle || String Concatenation to SQL Server

Microsoft SQL Server 2012 - Books Online

• + String Concatenation
• CONCAT Function
• SET CONCAT_NULL_YIELDS_NULL