Concatenating strings is a common task in SQL Server, especially when displaying full names or combining values from different columns. In this article, we will explore various methods to concatenate strings efficiently.

Method 1: Using the ‘+’ Operator

The simplest way to concatenate strings in SQL Server is by using the ‘+’ operator. For example:

SELECT 'FirstName' + ' ' + 'LastName' AS FullName

This will result in the full name being displayed as ‘FirstName LastName’.

Method 2: Concatenating Numbers

When concatenating numbers, we need to ensure that they are converted to strings. Otherwise, an error will occur. For example:

SELECT CAST(1 AS VARCHAR(10)) + ' ' + CAST(2 AS VARCHAR(10))

This will display ‘1 2’ as the concatenated result.

Method 3: Concatenating Values from Table Columns

We can also concatenate values from different columns in a table. For example:

SELECT FirstName + ' ' + LastName AS FullName FROM AdventureWorks2012.Person.Person

This will display the full names of individuals from the ‘Person’ table in the ‘AdventureWorks2012’ database.

Method 4: Using the CONCAT Function (SQL Server 2012 and later)

Starting from SQL Server 2012, a new function called CONCAT was introduced to concatenate strings efficiently. It eliminates the need for explicit casting or converting of values. For example:

SELECT CONCAT('FirstName', ' ', 'LastName') AS FullName

This will produce the same result as Method 1.

Method 5: Mixing Data Types with CONCAT Function

The CONCAT function can handle mixing different data types without any issues. For example:

SELECT CONCAT('FirstName', ' ', 1) AS FullName

This will display ‘FirstName 1’ as the concatenated result.

By using these methods, you can easily concatenate strings in SQL Server based on your specific requirements.

Stay tuned for more SQL Server tips and tricks in our SQL in Sixty Seconds series!