SQL Server: CONCAT Function

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

Description

In SQL Server (Transact-SQL), the CONCAT function allows you to concatenate strings together.

Syntax

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.

Note

• See also the + operator.

Applies To

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

Example

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

For example:

SELECT CONCAT('AODBA', '.com');
Output: 'AODBA.com'

SELECT CONCAT('Tech', 'On', 'The', 'Net', '.com');
Output: 'AODBA.com'

SELECT CONCAT('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.

For example:

SELECT CONCAT('Orange', ' ', 'Peach');
Output: '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:

'Orange Peach'

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');
Output: 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';
Output: 'AODBA.com'

You could concatenate three strings together using the + operator, as follows:

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