SQL Server: + Operator
This SQL Server tutorial 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 'TechOnTheNet' + '.com'; Result: 'TechOnTheNet.com' SELECT 'Tech' + 'On' + 'The' + 'Net' + '.com'; Result: 'TechOnTheNet.com' SELECT 'Tech ' + 'On ' + 'The ' + 'Net'; Result: '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'; Result: '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'; Result: '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'; Result: 'Let's learn SQL Server'