SQL Server: CONCAT Function
This SQL Server tutorial explains how to use the CONCAT function in SQL Server (Transact-SQL) with syntax and examples.
In SQL Server (Transact-SQL), the CONCAT function allows you to concatenate strings together.
The syntax for the CONCAT function in SQL Server (Transact-SQL) is:
CONCAT( string1, string2, ... string_n )
Parameters or Arguments
- string1, string2, … string_n
- The strings to concatenate together.
- See also the + operator.
The CONCAT function can be used in the following versions of SQL Server (Transact-SQL):
- SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012
Let’s look at some SQL Server CONCAT function examples and explore how to use the CONCAT function in SQL Server (Transact-SQL).
SELECT CONCAT('TechOnTheNet', '.com'); Result: 'TechOnTheNet.com' SELECT CONCAT('Tech', 'On', 'The', 'Net', '.com'); Result: 'TechOnTheNet.com' SELECT CONCAT('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.
SELECT CONCAT('Orange', ' ', 'Peach'); Result: 'Orange Peach'
In this example, we have used the second parameter within the CONCAT function to add a space character between the values Orange and Peach. This will prevent our values from being squished together.
Instead our result would appear as follows:
Here, we have concatenated the two values, separated by a space character.
Concatenate Single Quotes
Since the parameters within the CONCAT function are separated by single quotation marks, it isn’t straight forward how to add a single quotation mark character within the result of the CONCAT function.
Let’s look at a fairly easy example that shows how to add a single quote to the resulting string using the CONCAT function.
Based on the Excel spreadsheet above, we can concatenate a single quote as follows:
SELECT CONCAT('Let', '''', 's learn SQL Server'); Result: Let's learn SQL Server
In this example, we have used the second parameter within the CONCAT function to add a single quote into the middle of the resulting string.
Since our parameters are enclosed in single quotes, we use 2 additional single quotes within the surrounding single quotation marks to represent a single quote in our result as follows:
Then when you put the whole function call together:
SELECT CONCAT('Let', '''', 's learn SQL Server');
You will get the following result:
Let's learn SQL Server
Frequently Asked Questions
Question: Since the CONCAT function was introduced in SQL Server 2012, how do I concatenate strings together in earlier versions of SQL Server, such as SQL Server 2008 or 2005?
Answer:In any version of SQL Server, you can concatenate strings together using the + operator.
For example, you could concatenate two strings together using the + operator, as follows:
SELECT 'TechOn' + 'TheNet.com'; Result: 'TechOnTheNet.com'
You could concatenate three strings together using the + operator, as follows:
SELECT 'TechOnTheNet' + '.' + 'com'; Result: 'TechOnTheNet.com'