Concatenation

You can concatenate binary or character expressions, combining two or more character or binary strings, character or binary data, or a combination of them.

If you're concatenating a character string, enclose it in single quotation marks.

Concatenation has the following syntax:

(expression expression [ expression]...)

Here's how to combine two character strings:

SELECT ('abc'  'def')

------
abcdef

(1 row(s) affected)

This query displays California author names under the column heading Moniker in last-name, first-name order, with a comma and space after the last name:

SELECT Moniker = (au_lname + ', ' + au_fname)
FROM authors
WHERE state = 'CA'

Moniker
-------------------------
White, Johnson
Green, Marjorie
Carson, Cheryl
O'Leary, Michael
Straight, Dean
Bennet, Abraham
Dull, Ann
Gringlesby, Burt
Locksley, Charlene
Yokomoto, Akiko
Stringer, Dirk
MacFeather, Stearns
Karsen, Livia
Hunter, Sheryl
McBadden, Heather

(15 row(s) affected)

To concatenate numeric or date datatypes, you must use the CONVERT function:

SELECT 'The due date is ' + convert(varchar(30), pubdate)
FROM titles
WHERE title_id = 'BU1032'

---------------------------------------
The due date is Jun 12 1991 12:00AM

(1 row(s) affected)

The empty string (' ') is evaluated as a single space:

SELECT 'abc' + '' + 'def'

-------
abc def

(1 row(s) affected)