SQL Server CONCAT Function


By:

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.

CONCAT FUNCTION multiple data types

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 NULL values

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.

CONCAT SQL Server with quotes

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






Comments For This Article

















get free sql tips
agree to terms