SQL CONCAT Function

The SQL Server CONCAT function is used to combine two or more strings and returns a string. While performing reports or preparing data, merging or joining two or more table columns is a common factor. One of the most common scenarios is concatenating the first and last names, preparing a message from columns.

Unlike the + Operator, this SQL CONCAT treats NULL values as an Empty string while displaying the result. So, it is the straightforward approach to perform the string concatenation. The CONCAT function considers the order of the arguments and joins the text in the same order produced by the user.

SQL CONCAT Function Syntax

The syntax of the string CONCAT function for concatenating the string data is shown below.

SELECT CONCAT (String 1, String 2,..., String N)
FROM [Source]

This function accepts N number of string values or columns that you want to perform concatenation on. The CONCAT function requires at least two arguments to perform the concatenation and not more than 254 arguments. Remember, you must use a comma to separate the arguments.

Return Value: The SQL Server CONCAT function returns a string as an output by combining the given values. However, if there is any NULL value in the passed arguments, the result will become an empty string.

NOTE: Apart from the string data, the CONCAT() function also accepts other kinds of data types, such as integers, dates, etc. However, it implicitly converts all those arguments into a string data type before performing the data concatenation.

For this CONCAT function demonstration, we use a simple customer table with 10 records, DimCustomer, and factInternetSales tables.

SQL Server CONCAT Function Examples

As we said earlier, it will return the result by combining two or more texts. This section provides a series of examples to show the CONCAT function in action. To start with, we use variables as arguments, and the following query merges two and three string variables.

Here, we declared two Server str variables and assigned some text data. In the next statement, the SQL CONCAT function will combine the two variables, produce the string result, and assign a new name (Output) using ALIAS.

In the next line, without declaring the variables, we used this method on three string values by directly inserting them.

DECLARE @text1 varchar(50), 
        @text2 varchar(50)
SET @text1 = 'Learn ' 
SET @text2 = 'Microsoft Free'

SELECT CONCAT(@text1, @text2) AS 'Output' 

--Combine three Directly
SELECT CONCAT('Learn', ' Transact Query',' 2017') AS 'Result'

OUTPUT

Output
-------
Learn Microsoft Free

Result
------
Learn Transact Query 2017

Concatenating First Name and Last Name

In the previous example, we used the SQL CONCAT function on already declared variables, but you can use it on tables to combine multiple columns. In most tables, the First name and last name of an employee or customer will be stored in two separate columns. However, while performing the reports, you need the Full name.

In this example, we will use the FirstName and LastName columns present in the Customer table to get the Full name.

SELECT [FirstName]
      ,[LastName]
      ,CONCAT([FirstName],' ', [LastName]) AS [Full Name]
      ,[Profession]
 FROM [CustomerRecord]

TIP: We used ‘ ‘ to get the empty space between the first and last name. If you want a comma or something, replace the space with a comma.

Concatenating With Static String

Apart from combining table columns or variables, you can use the SQL Server CONCAT function to combine 3 or more columns along with static text or information to display meaningful information. Suppose you can combine the Customer Name and address fields to show the complete information about the customer and their address.

The query below shows how to use three columns and text in one statement. In this example, we used the VARCHAR columns, i.e., the First Name, Last Name, and Department name columns, and text = ‘is working as’.

SELECT *
      ,CONCAT(
         [FirstName],' ',[LastName],' is Working as',' ',[Profession]
       ) AS [Description]
FROM [CustomerRecord]

Handling Single Quotes

While performing the string concatenation, you must be careful with the single or double quotes. If you use the regular ”, the SQL CONCAT function considers it as the normal text. So, you must use ”’ ”’ to add a single quote and for the double quotes, use ‘” “‘.

SELECT CONCAT('ABC', ' DEF ', '''MNO''') AS Single;
SELECT CONCAT('ABC', ' DEF ','"MNO"') AS DoubleQ;
Single
------
ABC DEF 'MNO'

DoubleQ
--------
ABC DEF "MNO"

Line Feed (\n) and Carriage Return (\r) with the SQL CONCAT Function

The built-in CHAR function helps convert the ASCII values to characters. You can use the following codes to get the result. However, to see the actual output, change the Result type from table to Text or File. To do so, right-click the empty space in the query window, Result- To > Result To Text.

  • New Line or Line Feed = CHAR(10)
  • Carriage Return = CHAR(13)

To demonstrate the same, we used a simple example with a sample message displaying a welcome message. In the first statement, we used the CHAR(10) and the second one uses the CHAR(13). If you combine both of them, it creates a line break.

SELECT CONCAT('Hi', CHAR(10), 'Hello',  CHAR(10), 'Welcome',  CHAR(10),'Everyone') AS msg1;
SELECT CONCAT('Hi', CHAR(13), 'Hello',  CHAR(13), 'Welcome',  CHAR(13),'Everyone') AS msg2;
SELECT CONCAT('Hi', CHAR(10), CHAR(13),'Hello',  CHAR(10), CHAR(13),'Welcome',  CHAR(10),CHAR(13),'Everyone') AS msg3;

Result as Text

msg1
-------------------------
Hi
Hello
Welcome
Everyone

(1 row affected)

msg2
-------------------------
Hi
Hello
Welcome
Everyone

(1 row affected)

msg3
----------------------------
Hi

Hello

Welcome

Everyone

(1 row affected)

SQL CONCAT Function on Numeric Expression

As we mentioned earlier, the CONCAT function allows you to use INT or numeric values as the arguments. When you use a number, it will be implicitly converted to text (string) and return the concatenation, instead of performing addition.

SELECT CONCAT(16, 09, 2025, 10, 49) AS Data;
Data
--------------
16920251049

SQL CONCAT function on Numbers and Strings

In all the above examples, we used this method either completely on string columns or numbers. However, you can use the CONCAT function on a combination of both number and string columns.

The query below combines First Name, Last Name, and the Yearly Income column of the money data type.

SELECT *
      ,CONCAT(FirstName, ' ', LastName, ' Income: ', [Yearly Income]) AS CustInfo
  FROM [CustomerRecord]

TIP: In general, it converts the Income into a string. If there is any issue, use the CAST function, such as CAST([Yearly Income] AS VARCHAR).

SQL Server CONCAT Function Example

CONCAT VS + Operator

In the previous versions of Microsoft SQL Server, people used the + operator to perform string concatenation. The arithmetic + operator will perform addition on numbers and concatenation on string columns. However, while working with NULL values, you must use COALESCE or other NULL functions because the + operator returns NULL. With the introduction of a dedicated SQL CONCAT function, things changed.

From the query below, the first statement returns the Hello World message, whereas the second one returns NULL.

SELECT CONCAT('Hello ', NULL, 'World') AS Message;
SELECT 'Hello ' + NULL + 'World' AS Message;
Message
-----------
Hello World


Message
------------
NULL

Similarly, if you work on numeric columns, the CONCAT function joins two values, whereas the + operator performs addition. Here, the first one returns VARCHAR 1020 and the second one returns INT 30.

SELECT CONCAT(10, 20) AS num;
SELECT 10 + 20 AS num2;
num
------------------------
1020


num2
-----------
30

Concatenating Date and String Columns

This query shows how to perform the concatenation on DATE or DATETIME columns and string columns. Well, you can also combine the INTEGER and the SQL CONCAT function to combine a date and text to return the string results.

SELECT [CustomerKey]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[DateFirstPurchase]
      ,CONCAT(FirstName, ' ', LastName, ' First Purchase: ', DateFirstPurchase) AS Info
  FROM [AdventureWorksDW2022].[dbo].[DimCustomer]

Joining Tables

The CONCAT function also allows you to combine columns from multiple tables using the JOIN concept. The query below joins the customer and geography tables. Next, the CONCAT function combines FirstName, LastName, City (Geography table), and date columns.

SELECT [CustomerKey]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,City
      ,[DateFirstPurchase]
      ,CONCAT(FirstName, ' ', LastName, ' Living in ', City, ' First Purchase: ', DateFirstPurchase) AS Info
  FROM [DimCustomer]
  JOIN DimGeography ON DimCustomer.GeographyKey = DimGeography.GeographyKey

SQL CONCAT function handling NULL Values

While working with data, handling NULL values plays a crucial role, and the CONCAT function elegantly handles NULLs. Unlike other built-in functions, it treats a NULL value as an empty string. So, if the CONCAT function receives any argument as a NULL value, it simply returns an empty string and continues the concatenation. However, if all the arguments are NULL values, it returns an empty string.

For instance, the first statement returns Hello World Message, whereas the second one returns an empty string. In the first statement, it simply ignores NULLs.

SELECT CONCAT('Hello', NULL, 'World') AS Data;

SELECT CONCAT(NULL, NULL, NULL) AS Data;
Data
----------
HelloWorld

(1 row affected)

Data
----

To demonstrate the NULL values on table columns, we use the below CONCAT function query to combine First, Middle, and Last to get the Full Name without NULLs.

SELECT [CustomerKey]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS Name
      ,[DateFirstPurchase]      
  FROM [DimCustomer]
CONCAT Function handling NULL Values

SQL CONCAT function Best Practices

The following is the list of best practices that you must follow to get the best results from the SQL string CONTACT function.

  • This function only combines the given arguments but does not provide any separator between the text. While performing the concatenation, use either a space or a separator between two columns.
  • When you combine a larger number of columns, use symbols or special characters to make the complete text readable.
  • Although the CONCAT function treats NULL values as an empty string, use the COALESCE or IFNULL functions to add default text for null values.
  • Try to avoid a large number of strings in a single statement. It reduces the query performance and makes it less readable.
  • Use the CONCAT function in the WHERE clause, if it is strictly necessary.
Categories SQL