SQL CONCAT Function

The SQL CONCAT one of the String Function, which is used to combine two or more strings and returns string.

TIP: Unlike + Operator CONCAT Function will convert Null values into Empty string while displaying the result.

SQL CONCAT Function Syntax

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.

In this article we will show you, How to write Concat Function in SQL Server with example. For this, We are going to use the below shown data

SQL CONCAT Function Example 1

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

T-SQL CODE

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, 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.

T-SQL CODE

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

SQL STRING CONCAT Function Example 3

The 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’.

T-SQL CODE

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

OUTPUT

Thank You for Visiting Our Blog