By: Daniel Calbimonte
The CONCAT function is used to concatenate 2 or more strings.
Syntax
CONCAT(stringToConcatenate1, stringToConcatenate1 [,stringToConcatenateN])
Parameters
- stringToConcatenate1 - This is the first string that we want to concatenate.
- stringToConcatenate2 - This is the second string that we want to concatenate.
- stringToConcatenateN - This is the Nth string that we want to concatenate. You can concatenate as many strings as you want up to a maximum of 254.
Simple CONCAT example
Below is a simple example of using CONCAT. We will concatenate 2 simple strings.
SELECT CONCAT('HELLO',' WORLD')
This returns a HELLO WORLD message.
HELLO WORLD
Using CONCAT against a Table and Column
The next example will use the AdventureWorks sample database and concatenate data with different data types. The example will show a concatenation of the FirstName, LastName, ModifiedDate and BusinessEntityID.
SELECT CONCAT('Fullname: ',[FirstName],' ',[LastName],' Modified Date: ', ModifiedDate,' BusinessEntityID:', BusinessEntityID) AS PersonInfo
FROM [Person].[Person]
The output will be something like this.
Note: In this above example, we are concatenating strings, dates and numbers. This is something that with the old concatenation operator (+) was not possible without using CONVERT or CAST function.
Using CONCAT with NULL Values
SELECT CONCAT('Fullname: ',[FirstName],' ',NULL,[LastName]) AS PersonInfo
FROM [Person].[Person]
Note that the NULL value is ignored and the data is still concatenated as shown below.
CONCAT with Commas and Single Quotes
This example will show how to concatenate strings with commas and single quotes.
SELECT CONCAT('This example contains, and ','''single quotes''') AS text
FROM [Person].[Person]
Note that the comma inside the quotes is treated like any other character whereas for the single quotes to show up in the output, this requires triple single quotes on both sides of the text.
Error message when using CONCAT function
A common error message you may see is the following:
'CONCAT' is not a recognized built-in function name
This error message occurs in old SQL Server versions like SQL Server
2008 R2 or older, where the CONCAT function did not exist. If you get this error, check your SQL Server version with SELECT
@@VERSION.
If your SQL Server version is older, you could upgrade to a newer version. The CONCAT function was first introduced in SQL Server 2012.
Related Articles
- Using SQL Server Concatenation Efficiently
- Concatenate SQL Server Columns into a String with CONCAT()
- CONCAT SQL Function in SQL Server
- Multiple Ways to Concatenate Values Together in SQL Server
- Using SQL Server Concatenation Efficiently
Last Update: 11/1/2021
