SQL CONCAT Function

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

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

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

Sql Concat Function Example 1

The String Concat function returns the string by combining two or more strings. The following string query Concat two string variables, and three strings

DECLARE @String1 varchar(50), 
        @String2 varchar(50)
SET @String1 = 'Learn ' 
SET @String2 = 'SQL Server'

SELECT CONCAT(@String1, @String2) AS 'SQL Concat' 

--Concating three String Directly
SELECT CONCAT('Learn', ' SQL Server',' 2014') AS 'SQL Concat'

Here, we declared two SQL Server string variables and assigned some data.

DECLARE @String1 varchar(50), 
        @String2 varchar(50)
SET @String1 = 'Learn ' 
SET @String2 = 'SQL Server'

In the below statement, the Sql Server String Concat function will combine the two variables and produce the string result, and assigned a new name using ALIAS.

SELECT CONCAT(@String1, @String2) AS 'SQL Concat'

In the next line, We used the Concat String Function to combine the three string values directly

--Concating three String Directly
SELECT CONCAT('Learn', ' SQL Server',' 2014') AS 'SQL Concat'

Concat Function Example 2

The String Concat function also allows you to combine multiple columns. In this concat example, we will combine the First name and Last Name columns present in the Employe column to get Full name.

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

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

String Concat Function Example 3

The String Concat function in Sql Server also allows us to combine columns and string text in one statement. In this example, we concatenate the First name, Last Name, and Department name columns, and string text = ‘is working as’.

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,CONCAT(
        [FirstName],' ',[LastName],' is Working as',' ',[DepartmentName]
       ) AS [Description]
 FROM [Employe]