SQL CONCAT Function

The SQL CONCAT one of the String Function, which is used to combine two or more strings and returns string. Unlike + Operator, concat Function will convert Null values into Empty string while displaying the result.

In this article we will show you, How to write Concat function in SQL Server with example. The basic syntax of the Concat function in SQL Server is as shown below:

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

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

Sql Concat Function Example 1

The String Concat function simply returns the string by combining two or more strings. The following query will show multiple ways to use Concat function.

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

Below lines of code is used to declare two string variables and assigning them some data.

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

Below statement, Sql Server String Concat function will combine the two variable and produce the string result. We also assigned new name to that result using SQL ALIAS Column.

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

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

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

Sql Concat Function Example 2

The Concat function also allows you to combine multiple columns. In this example,We are going 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 empty space. If you want comma or something just replace empty space with comma

String Concat Function Example 3

The String Concat function in Sql Server also allows you to combine columns and string text in one statement. In this example,We are going combine the First name, Last Name and Department name columns present in the Employe column and string text = ‘is working as’.

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

OUTPUT

Thank You for Visiting Our Blog