SQL Concatenation operators

SQL SELECT Statement

If you wish to concatenate 2 strings i.e. combine 2 strings, then you can use the Concatenation Operator which is denoted by + symbol.

+ Symbol

This is used to combine 2 strings into a single string

Syntax
expression1 + expression2

Note For strings, when you use the + operator in the SELECT clause, it acts like string concatenation.

SELECT Title, FirstName, MiddleName, LastName, Title + FirstName + ' ' + MiddleName + ' ' + LastName AS FullName
FROM Person.Person;

You can also code like this –

SELECT 'Full Name is: ' +  Title + FirstName + ' ' + MiddleName + ' ' + LastName AS FullName
FROM Person.Person;

Note If any one of the string contains a NULL value, then the final string also becomes NULL.

For Strings, You can also use + operator in the WHERE clause but it is rarely used –

SELECT Title, FirstName, MiddleName, LastName, Title + FirstName + ' ' + MiddleName + ' ' + LastName AS FullName
FROM Person.Person
WHERE Title + FirstName + ' ' + MiddleName + ' ' + LastName = 'Ms.Gail A Erickson';

Also, it is important to note that + operator will act as a concatenation operator when you use this with strings but it will add 2 or more numeric values when used with Numeric values.

SELECT StandardCost + ListPrice As SumOfStdCostAndListPrice
FROM Production.Product;

concat() Function

There is a string function Concat() which can also be used for string concatenation. This will add 2 or more strings.

Syntax
Concat(string1, string2,…stringn)

SELECT Title, FirstName, MiddleName, LastName, Concat(Title, FirstName, ' ', MiddleName, ' ', LastName) AS FullName
FROM Person.Person
WHERE Title + FirstName + ' ' + MiddleName + ' ' + LastName = 'Ms.Gail A Erickson';

concat_ws() Function

There is a way to add a separator during the concatenation process using the concat_ws() function.

Example

Select FirstName, BusinessEntityID, CONCAT_WS('.','adventure-works','com') as domain
fROm Person.Person;

Combining concat() function with concat_ws() function

You can nest concat() function within another concat() function.

Similarly, you can also nest concat_ws() function within another concat_ws() function.

It is also easy to combine concat() function with concat_ws() function.

Select FirstName, BusinessEntityID, concat(FirstName, BusinessEntityID, '@',CONCAT_WS('.','adventure-works','com')) AD Personemail, CONCAT_WS('.','adventure-works','com') as domain
fROm Person.Person;