SQL CONCAT Function

The SQL CONCAT function is a String Function 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]

As you can see from the above Concat syntax, this function accepts N number of string values. For this SQL Server Concat function demonstration, we are going to use the below-shown data

Sql Concat Function Example 1

The String Concat function returns the string by combining two or more strings. The following 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'

OUTPUT

ANALYSIS

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 Concat function also allows you to combine multiple columns. In this example, we are going to 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]

OUTPUT

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]

OUTPUT