SQL CONCAT Function

The SQL CONCAT function is used to combine two or more strings and returns string. Unlike + Operator, this method will convert Null values into an Empty string while displaying the result. The syntax of this is

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

This function accepts N number of string values. For this SQL Server Concat function demonstration, we use this data

Sql Concat Function Example

As we said earlier, it will return the result by combining two or more texts. The following query merge two, and three string variable.

Here, we declared two Server string variables and assigned some data. In the next statement, the function will combine the two variables and produce the string result, and assigned a new name using ALIAS.

In the next line, We used this method to combine the three string values directly

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'

Example 2

The Sql Server concat function also allows you to combine multiple columns. In this example, we will combine the FirstName and LastName columns present in the Employe to get Full name.

SELECT [FirstName]
      ,[LastName]
      ,CONCAT([FirstName],' ', [LastName]) AS [Full]
      ,[DepartmentName]
 FROM [Employe]

TIP: We used this ‘ ‘ to get the empty space. If you want comma or something, replace empty space with comma

The Concat function in Sql Server also allows us to combine columns and text in one statement. In this example, we combine 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',' ',[DepartmentName]
       ) AS [Description]
 FROM [Employe]