SQL CONCAT Function

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

The syntax of the SQL string concat function is shown below.

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

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

SQL Server Concat Function Example

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

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

In the next line, We used this method on 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'

SQL Concat String with separator

The Concat function also allows you to combine multiple columns. In this example, we will use the FirstName and LastName columns present in the Employe to get the 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 a comma or something, replace empty space with a comma

It also allows us to use 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',' ',[DepartmentName]
       ) AS [Description]
 FROM [Employe]