In SQL Server, the CONCAT function is a powerful tool that allows you to concatenate two or more character expressions into a single string. In this article, we will explore the syntax, working mechanism, and other details of the CONCAT function, and provide various examples to illustrate its usage.

Introduction to SQL String Data Types

Before diving into the CONCAT function, it’s important to understand the different SQL string data types. In SQL Server, string data types can be categorized into two groups:

• Character strings data types that store non-Unicode character data
• Unicode character strings data types that store Unicode character data

Each data type has its own minimum and maximum limits, ranging from 0 to a maximum of 2^31 characters. It’s worth noting that the text and ntext data types are deprecated, and it is recommended to use nvarchar(max) or varchar(max) instead.

Syntax of the CONCAT Function

The CONCAT function in SQL Server has the following syntax:

CONCAT(string_value1, string_value2 [, string_valueN])

The CONCAT function requires at least two parameters and can accept a maximum of 254 parameters. It concatenates the specified string values into a single string.

Examples of Using the CONCAT Function

Let’s explore some examples to understand how the CONCAT function works:

SELECT CONCAT('Think', 'green') AS FullString

In this example, the CONCAT function joins the strings ‘Think’ and ‘green’, resulting in the string ‘Thinkgreen’.

SELECT CONCAT('If', ' you', ' save', ' a', ' tree', ' you', ' save', ' a', ' life') AS FullString

In this example, the CONCAT function concatenates more than two strings, resulting in the string ‘If you save a tree you save a life’.

The CONCAT function can also be used to concatenate variables:

DECLARE @Str1 AS VARCHAR(100) = 'Think'
DECLARE @Str2 AS VARCHAR(100) = '-'
DECLARE @Str3 AS VARCHAR(100) = 'green'
SELECT CONCAT(@Str1, @Str2, @Str3) AS ResultString

In this example, the CONCAT function concatenates the values of the variables @Str1, @Str2, and @Str3, resulting in the string ‘Think-green-green’.

Concatenating Numerical Expressions with CONCAT

The CONCAT function in SQL Server can also be used to join numerical values. In the following example, we will join three different integer values:

SELECT CONCAT(11, 33, 99) AS Result

The CONCAT function automatically converts the numerical expressions to strings, resulting in the string ‘113399’.

However, if you want to concatenate numerical expressions using the plus sign (+), you need to explicitly convert them to string data types:

SELECT CAST(11 AS VARCHAR(10)) + CAST(33 AS VARCHAR(10)) + CAST(99 AS VARCHAR(10)) AS TrueResult

In this example, the numerical expressions are converted to strings before concatenation, resulting in the string ‘113399’.

It’s important to note that if you try to concatenate numerical expressions without converting them to strings, the result will be a mathematical addition:

SELECT 11 + 33 + 99 AS WrongResult

In this example, the numerical expressions are added together, resulting in the value 143.

Understanding the Execution Plan of CONCAT

When using the CONCAT function, it’s important to understand the execution plan and how the function operates behind the scenes. Let’s take a closer look at the execution plan of a query that uses the CONCAT function:

SELECT CONCAT(Number_1, Number_2, Number_3) FROM Test_NumericValue

The execution plan reveals that the CONCAT function performs a data conversion operation, converting the integer (INT) data type expressions to strings (VARCHAR) before concatenation. This implicit conversion is performed automatically by SQL Server.

It’s worth mentioning that if any non-string data type parameter is passed to the CONCAT function, SQL Server will perform an implicit conversion to convert the parameter into a string data type before concatenation.

Handling NULL Values with CONCAT

In terms of the CONCAT function, if a NULL value is used as a parameter, it is converted to an empty string. For example:

SELECT CONCAT('Think', NULL, 'green') AS FullString

In this example, the NULL expression used in the CONCAT function does not affect the result, and the function behaves as if the NULL value was an empty string.

If all parameters passed to the CONCAT function are NULL, the result will also be an empty string:

SELECT CONCAT(NULL, NULL, NULL) AS FullString

As a result, we can conclude that NULL values do not affect the output of the CONCAT function.

Using Line Feed and Carriage Return with CONCAT

The CONCAT function can also be used to include line feed (\n) and carriage return (\r) characters in the concatenated string. The CHAR function can be used to convert ASCII numbers to character values.

For example, the following ASCII codes can be used to get a new line with the CHAR function:

• 10: LF (Line Feed)
• 13: CR (Carriage Return)

By using the CHAR function, we can concatenate strings with line breaks:

SELECT CONCAT('Make', CHAR(13), 'every', CHAR(13), 'drop', CHAR(13), 'of', CHAR(13), 'water', CHAR(13), 'count') AS Result

In this example, the CONCAT function concatenates the strings line by line, resulting in:

Make
every
drop
of
water
count

By replacing CHAR(13) with CHAR(10), we can achieve the same result with a different line break character:

SELECT CONCAT('Make', CHAR(10), 'every', CHAR(10), 'drop', CHAR(10), 'of', CHAR(10), 'water', CHAR(10), 'count') AS Result

Additionally, we can use both CHAR(13) and CHAR(10) together to generate a line break:

SELECT CONCAT('Make', CHAR(10), CHAR(13), 'every', CHAR(10), CHAR(13), 'drop', CHAR(10), CHAR(13), 'of', CHAR(10), CHAR(13), 'water', CHAR(10), CHAR(13), 'count') AS Result

This usage type could be a good option when we want to generate a line break.

Conclusion

In this article, we have explored the CONCAT function in SQL Server and learned how to use it with various examples. The CONCAT function is a valuable tool for concatenating strings in SQL and can be used to join character expressions, numerical values, and handle NULL values. Understanding the syntax and behavior of the CONCAT function can greatly enhance your SQL Server skills and enable you to manipulate strings effectively.