SQL 2005+

SQL Server String Concatenation Operator

by Richard Carr, published at http://www.blackwasp.co.uk/SQLConcatenationOperator.aspx

The thirty-third part of the SQL Server Programming Fundamentals tutorial describes the string concatenation operator. This operator allows two strings to be combined. The operator can be used with literals, variables and columns in queries.

Concatenation

Earlier in the SQL Server Programming Fundamentals tutorial, whilst examining SQL Server queries, I briefly described the string concatenation operator. This operator allows two strings to be combined by appending the contents of one to the end of the other.

The two strings must be either character or binary data types and may not be image, text or ntext types. In addition, the two strings must either be of the same type or one of the strings must be able to be implicitly converted to the type of the other. For unsupported types to be included in a concatenation operation, they must first be converted to one of the supported types. Type conversion will be examined later in the tutorial.

Using the String Concatenation Operator

Some of the examples below use data from the JoBS database. This is the database that has been created and populated with data during the course of this tutorial. If you do not have an up-to-date copy of the database you can create one by executing the script that may be downloaded using the link at the top of this page.

Basic Concatenation

The concatenation operator uses the plus symbol (+) with two operands. The first operand will be the start of the resultant string and the second will be its end. A simple example of the operator's use can be shown with a print statement:

PRINT 'Black' + 'Wasp'  -- 'BlackWasp'

We can also use the operator within a query or other T-SQL statement. The following query returns the full names for all of the customers in the JoBS database by combining their first and last names with a separating space:

SELECT FirstName + ' ' + LastName FROM Customers

Concatenation of NULL Values

As we have seen, a NULL value represents a value that is undefined. When concatenating two strings, it is logical that combining a known value with NULL will therefore yield an undefined value. The usual behaviour when concatenating strings where one value is NULL is that the resultant string is also NULL. For example:

SELECT 'Known' + NULL   -- Returns NULL (usually)

The reason that this is described as 'the usual behaviour' is that you can treat NULLs as empty strings for concatenation by changing the CONCAT_YIELDS_NULL setting. However, this should be discouraged, as Microsoft has indicated that the setting will be removed from future versions of SQL Server.

Operator Precedence

We can now add the string concatenation operator to the table of precedence. Note that the concatenation operator is less important than arithmetic addition but more important than subtraction.