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
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:
select @v + @w
select @v || "abdcef"
select "xyz" + @w
