In Microsoft SQL Server, there are often situations where you need to concatenate data in SQL queries or stored procedures to create a single long string instead of having separate columns. In this SQL tutorial, we will explore various ways to achieve this using the Transact-SQL (T-SQL) language.

Concatenating Strings with the + Operator

The most common way to concatenate strings in T-SQL is by using the + operator. Let’s consider an example where we want to concatenate the FirstName and LastName columns from the [Person].[Person] table:

SELECT FirstName + ' ' + LastName AS fullname
FROM [Person].[Person]

This query will return the concatenated full name with a space between the first and last names. However, it’s important to note that if any of the concatenated strings have a NULL value, the entire result will be NULL. To handle this, we can use the COALESCE function to convert NULL values to empty strings:

SELECT FirstName + ' ' + COALESCE(MiddleName, '') + ' ' + LastName AS fullname
FROM [Person].[Person]

By using COALESCE, we ensure that even if the MiddleName column has NULL values, the concatenation will still produce the desired result.

Concatenating Numbers and Strings

In addition to concatenating strings, we can also concatenate numbers with strings. However, when using the + operator, we need to ensure that the data types are compatible. For example, if we try to concatenate an integer column with a string, we will encounter an error:

SELECT 'The expiration date is: ' + [ExpYear] AS results
FROM [Sales].[CreditCard]

To fix this issue, we can use the CAST or CONVERT functions to explicitly convert the integer to a string:

SELECT 'The expiration date is: ' + CAST([ExpYear] AS varchar(4)) AS results
FROM [Sales].[CreditCard]

Alternatively, we can use the TRY_CAST or TRY_CONVERT functions, which handle errors gracefully by returning NULL instead of throwing an error:

SELECT 'The expiration date is: ' + TRY_CAST([ExpYear] AS varchar(4)) AS results
FROM [Sales].[CreditCard]

Both CAST and TRY_CAST functions convert the integer into a string, while CONVERT and TRY_CONVERT functions provide similar functionality.

Concatenating Data with the += Operator

In addition to the + operator, we can also use the += operator to concatenate a previous value with a new value. This can be useful when working with variables. For example:

DECLARE @msj varchar(30) = 'hello '

SET @msj += 'MSSQLTIPS'

SELECT @msj AS mymsj

In this example, the += operator appends the value ‘MSSQLTIPS’ to the existing value of the @msj variable, resulting in ‘hello MSSQLTIPS’.

Concatenating Data with the CONCAT Function

Another way to concatenate data in SQL Server is by using the CONCAT function. This function allows you to concatenate multiple values or parameters separated by commas. It automatically handles NULL values without the need for additional functions like COALESCE:

SELECT CONCAT(FirstName, ' ', LastName) AS fullname
FROM [Person].[Person]

The CONCAT function can also be used to concatenate numeric values with strings:

SELECT CONCAT('The expiration date is: ', [ExpYear]) AS results
FROM [Sales].[CreditCard]

As you can see, the CONCAT function simplifies the concatenation process, especially when dealing with NULL values or different data types.

By using the various concatenation techniques discussed in this tutorial, you can effectively combine data in SQL Server queries and stored procedures to meet your specific requirements.