SQL Server: + Operator

In this post explains how to use the concatenate operator (+ operator) in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the + operator allows you to concatenate 2 or more strings together.

Syntax

The syntax for the + operator in SQL Server (Transact-SQL) is:

string1 + string2 + string_n

Parameters or Arguments

string1

The first string to concatenate.

string2

The second string to concatenate.

string_n

The nth string to concatenate.

Note

• See also the CONCAT function.

Applies To

The + operator can be used in the following versions of SQL Server (Transact-SQL):

• SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let's look at some SQL Server + operator examples and explore how to use the + operator in SQL Server (Transact-SQL).

For example:

SELECT 'AODBA' + '.com';
Output: 'AODBA.com'

SELECT 'Tech' + 'On' + 'The' + 'Net' + '.com';
Output: 'AODBA.com'

SELECT 'Tech ' + 'On ' + 'The ' + 'Net';
Output: 'Tech On The Net'

Concatenate Space Characters

When you are concatenating values together, you might want to add space characters to separate your concatenated values. Otherwise, you might get a long string with the concatenated values running together. This makes it very difficult to read the results.

Let's look at an easy example.

We can concatenate a space character using the + operator.

For example:

SELECT 'Jane' + ' ' + 'Smith';
Output: 'Jane Smith'

In this example, we have used the + operator to add a space character between the values Jane and Smith. This will prevent our values from being squished together.

Instead our result would appear as follows:

'Jane Smith'

You would more commonly use the + operator to concatenate a space character when you are concatentating multiple fields together.

For example:

SELECT first_name + ' ' + last_name AS contact_name
FROM contacts;

This example would return a result set with one column that consisted of the first_name and last_name fields (separated by a space) from the contacts table. The column in the result set would be aliased as contact_name.

Concatenate Single Quotes

Since the + operator will concatenate string values that are enclosed in single quotes, it isn't straight forward how to add a single quote character within the result of the concatenated string.

Let's look at a fairly easy example that shows how to add a single quote to the resulting string using the + operator.

For example:

SELECT 'Let''s' + ' learn SQL Server';
Output: 'Let's learn SQL Server'

Since our string values are enclosed in single quotes, we use 2 additional single quotes within the surrounding quotes to represent a single quotation mark in the resulting concatenated string.

If you wanted to separate out the single quote from the other string values, you could also rewrite this query as follows:

SELECT 'Let' + '''' + 's' + ' learn SQL Server';
Output: 'Let's learn SQL Server'