Concatenating expressions

You can concatenate binary or character expressions; that is, you can combine two or more character or binary strings, character or binary data, or a combination of them with the + string concatenation operator. The maximum length of a concatenated string is 16384 bytes.

You can concatenate binary and varbinary columns and char, unichar, nchar, varchar, univarchar, and nvarchar columns. If you concatenate unichar and univarchar with char, nchar, nvarchar, and varchar, the result is unichar or univarchar. You cannot concatenate text, unitext, or image columns.

The concatenation syntax is:

select (expression + expression [+ expression]...) 

For example, to combine two character strings:

select ("abc" + "def") 
------- 
abcdef 
 
(1 row affected) 

To combine California authors’ first and last names under the column heading Moniker in last name-first name order, with a comma and space after the last name, enter:

select Moniker = (au_lname + ", " + au_fname) 
from authors 
where state = "CA" 
Moniker 
------------------------------------------------- 
White, Johnson 
Green, Marjorie 
Carson, Cheryl 
O’Leary, Michael 
Straight, Dick 
Bennet, Abraham 
Dull, Ann
...

When a string function accepts two character expressions but only one expression is unichar, the other expression is “promoted” and internally converted to unichar. This follows existing rules for mixed-mode expressions. However, this conversion may cause truncation, since unichar data sometimes takes twice the space.

To concatenate noncharacter or nonbinary columns, use the convert function. For example:

select "The due date is " + convert(varchar(30), 
    pubdate)  
from titles 
where title_id = "BU1032" 
--------------------------------------- 
The due date is Jun 12 2006 12:00AM             

Adaptive Server evaluates the empty string (“ ” or ‘ ’) as a single space. This statement:

select "abc" + "" + "def"

produces:

abc def

Concatenation operators and LOB locators

The + and || Transact-SQL operators accept LOB locators as expressions for a concatenation operation. The result of a concatenation operation involving one or more locators is a LOB locator with the same datatype as that referenced by the input locator.

For example, assume that @v and @w are text locator variables. These are valid concatenation operations: