SQL Server CONCAT() Function: Concatenates Strings

The CONCAT() function joins two or more string expressions in an end-to-end manner and returns a single string.

CONCAT(string1, string2 [, stringN])

Parameters

string: At least two or more string values must be passed, else it returns an error. You can pass a maximum of 254 string expressions.

Return Value

Returns a concatenated string.

Note: The CONCAT() function implicitly converts all the arguments to string type before concatenating. It converts NULL value to an empty string with varchar(1) data type.

Example 1:

The following example concatenates two strings.

SELECT CONCAT('Good ', 'Morning!') as Greetings;

Example 2:

In the below example, three strings 'Good Morning!', a space, and 'Have a nice day,' are concatenated to return a single string expression.

SELECT CONCAT('Good Morning!, ' ', 'Have a nice day.') as Greetings;

Example 3:

The CONCAT() function can be used to database table's columns. The following join the FirstName column, a string with white space, and the LastName column of the Employee table.

SELECT CONCAT(emp.FirstName,' ' , emp.LastName) as EmployeeName;
FROM Employee emp;

CONCAT_WS()

Use the CONCAT_WS() method to concatenate two or more strings with the specified separator.

In the below example, columns FirstName and LastName values are joined with a comma separator.

SELECT CONCAT_WS(',', emp.FirstName, emp.LastName) as EmployeeName;
FROM Employee emp;