- SQL Server (starting with 2008)
- Azure SQL Database
- Azure SQL Data Warehouse
- Parallel Data Warehouse
An operator in a string expression that concatenates two or more character or binary strings,
columns, or a combination of strings and column names into one expression (a string operator).
expression + expression
-- Azure SQL Data Warehouse and Parallel Data Warehouse
expression
+
expression
expression:Is any valid expression of any one of the data types in the character and binary data type category, except the image, ntext, or text data types.
Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression.
An explicit conversion to character data must be used when concatenating binary strings and any characters between the binary strings.
The following example shows when CONVERT, or CAST, must be used with binary concatenation and when CONVERT, or CAST, does not have to be used.
It is important to know how to concatenate strings together in SQL Server.
Every piece of the string that is concatenated must be a character data type and String literals must be enclosed in quotes.
Unlike other programming languages, such as Visual Basic (which uses the ampersand
&, SQL Server uses the plus sign (“+”) to concatenate strings together.
If you are using string literals to concatenate your strings, make sure that your quotes are in the correct place.
Otherwise, SQL Server might treat your variable names as string literals.
Using Dynamic SQL allows you to use logic in your queries, too. Because building Transact-SQL statements dynamically is nothing more than building a string, you can concatenate strings around any valid logic constructs, like
IF…THEN
ELSE
, loops, and other conditional constructs.
Each variable that holds your Transact-SQL statements cannot contain more than 4000 characters. Therefore, if your statement is longer, you must issue multiple variables to the
EXEC function.