SQL Server: + Operator
This SQL Server tutorial explains how to use the concatenate operator (
+ operator) in SQL Server (Transact-SQL) with syntax and examples.
In SQL Server (Transact-SQL), the
+ operator allows you to concatenate 2 or more strings together.
The syntax for the
+ operator in SQL Server (Transact-SQL) is:
string1 + string2 + string_n
Parameters or Arguments
- The first string to concatenate.
- The second string to concatenate.
- The nth string to concatenate.
- See also the CONCAT function.
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
Let’s look at some SQL Server + operator examples and explore how to use the
+ operator in SQL Server (Transact-SQL).
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
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:
You would more commonly use the
+ operator to concatenate a space character when you are concatentating multiple fields together.
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
+ 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
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'