String Concatenation Operator

You can use both the + and || (double-pipe) string operators to concatenate two or more character or binary expressions.

For example, the following displays author names under the column heading Name in last-name first-name order, with a comma after the last name; for example, “Bennett, Abraham.”:

select Name = (au_lname + ", " + au_fname) 
    from authors

This example results in "abcdef", "abcdef":

select "abc" + "def", "abc" || "def"

The following returns the string “abc def”. The empty string is interpreted as a single space in all char, varchar, unichar, nchar, nvarchar, and text concatenation, and in varchar and univarchar insert and assignment statements:

select "abc" + "" + "def"

When concatenating non-character, non-binary expressions, always use convert:

select "The date is " + 
    convert(varchar(12), getdate())

A string concatenated with NULL evaluates to the value of the string. This is an exception to the SQL standard, which states that a string concatenated with a NULL should evaluate to NULL.