Esat Erkec
Find out the implicit conversion in CONCAT function

An overview of the CONCAT function in SQL with examples

January 13, 2020 by

In this article, we will explore the syntax, working mechanism and other details of the CONCAT function in SQL and we will also make up various different examples about it.

Introduction

A string is a set of characters that helps to declare the texts in the programming languages. In terms of SQL Server, we can categorize SQL string data types into different two groups. These are:

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

    Character strings data types

    Data type

    Min limit

    Max limit

    char

    0 chars

    8000 chars

    varchar

    0 chars

    8000 chars

    varchar (max)

    0 chars

    2^31 chars

    text

    0 chars

    2,147,483,647 chars

  • Unicode character strings data types that store Unicode character data

    Unicode character string data types

    Data type

    Min limit

    Max limit

    nchar

    0 chars

    4000 chars

    nvarchar

    0 chars

    4000 chars

    ntext

    0 chars

    1,073,741,823 char

We have to take into account one point about the text and ntext data types. These data types are deprecated, and for this reason, we should try not to use these data types. We can use nvarchar(max) or varchar(max) instead.

SQL string functions are used to manipulate the character expressions or to obtain various information about them. CONCAT function in SQL is one of the most useful members of these functions. CONCAT function is a SQL string function that provides to concatenate two or more than two character expressions into a single string. Now, we will go into the point with a simple example.

CONCAT function syntax

The syntax of the function looks like as follows:

CONCAT ( string_value1, string_value2 [, string_valueN ] )

The CONCAT function at least requires two parameters and this function can accept a maximum of 254 parameters.

CONCAT function examples

In this example, we will join Think and green strings with the CONCAT function:

Example of the CONCAT function in SQL

As we can see clearly in this first example, the CONCAT function joined these two strings and we obtained the Thinkgreen string.

In this second example, we will join 7 strings:

Usage of the CONCAT function in SQL with multiple parameter

In this second example, the CONCAT function concatenated more than two strings and the result was If you save a tree you save a life.

In addition, we can concatenate the variables with this function:

Usage of the CONCAT function in SQL with variables

Concatenating numerical expressions with CONCAT function in SQL

CONCAT function also has the capability to join the numeric values. In the following example, we will join three different integer values:

Usage of the CONCAT function for numeric values

As we can see, we did not use any CAST or CONVERT function to join these numerical expressions with the function. On the other hand, if we want to concatenate these expressions with (+) plus sign, we need to convert them to string data types. Otherwise, the result of the concatenation operation will be incorrect:

Concatenate numerical values with a plus sign

The following example demonstrates the concatenating numerical expressions with (+) plus without any data conversion so the output will be a mathematical addition:

Concatenate numerical values with a plus sign without data conversion.

Now, let’s research and try to understand what is happening behind the scene while the numerical expressions concatenation process with CONCAT function.

Firstly, we will create a test table in order to insert some numerical expressions. The following script will create a Test_NumericValue table:

In the second step, we will insert test data to this table:

Now, we will execute the below SELECT statement in the ApexSQL Plan:

Generating execution plan of the CONCAT function in SQL

In this step, we will analyze the actual execution plan details of the query. The reason why we analyze the execution plan is that all details about the query processing operation are hidden in it:

Execution plan of the CONCAT function in SQL

Let us briefly explain the query plan mentioned above. The table scan operator indicates that the query optimizer is required to read whole data of the Test_NumericValue because it does not contain any index. However, this isn’t a problem for this table because it contains only one row. On the other hand, if we find out this operator for any table which has a huge number of rows, this situation may indicate a performance issue. Compute Scalar operator performs a scalar computation and takes the task of completing the CONCAT function operation for this query execution. In the final step of the execution plan, the SELECT operator represents the result of the query. A warning sign shows on the SELECT operator. We will mention the details of this warning sign in the following tip. Now, we will click the Operations tab to find out more details about the Compute Scalar operator.

Find out the implicit conversion in CONCAT function

As we can clearly see, a data conversion occurred while the query was executing. Particularly for this query, the integer (INT) data type expressions are converted to strings (VARCHAR). With this example, we demonstrated that the data conversion operation is made by the CONCAT function. In the following tip, we will highlight another issue about the execution plan that is CONVERT_IMPLICIT operation.

Tip: CONCAT function in SQL performs implicit conversion if any non-string data type parameter passes.

As we mentioned above, there is a warning sign shown on the SELECT operator in the execution plan. The reason for this sign is that if we use any non-character parameters in the CONCAT function, these parameters will be converted into the string data type automatically by SQL Server. Then the string combining operation will be performed. Now, we will reinforce this theoretical information with an example:

Firstly, we will execute the following query in order to prepare the test environment:

The following query will try to concatenate 4 different data typed expressions. These are VARCHAR, INT, BIGINT, and DATETIME.

Now, we will execute the following query:

Joining the different data types with CONCAT function

As we can see, all expressions with different data types are concatenated without any error. Now, we will analyze the execution plan of the query:

CONCAT function query execution plan

When we hover the cursor on the SELECT operator icon, a pop-up window appears and the implicit conversion issue is showing obviously. Implicit conversion is a data conversion operation that is made by SQL Server automatic when it required. In addition, you can look at the Implicit conversion in SQL Server article to learn more details about the implicit conversion notion.

NULL value and CONCAT function in SQL

NULL is a special pointer that identifies the value that is unknown or does not exist in SQL Server. In terms of CONCAT function, if we use a NULL value as a parameter to CONCAT function, it converts the NULL values to an empty string. Now, we will make an example of it:

CONCAT function in SQL and NULL value

As we can see, the NULL expression used in the CONCAT function did not affect the result of the function and behaved like an empty string. In addition, if we pass all parameters as NULL, the result of the function will be an empty string. Now, we will make an example of this:

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

How to use line feed (\n) and carriage return (\r) with CONCAT function

CHAR function enables to convert ASCII numbers to character values. The following ASCII codes can be used to get a new line with CHAR function in SQL:

Value

Char

Description

10

LF

Line Feed

13

CR

Carriage Return

We can get a new line when we concatenate the strings with the following CHAR functions:

  • CHAR (10): New Line / Line Feed
  • CHAR (13): Carriage Return

For example, the following query results in the concatenated string line by line with CHAR(13) function:

CONCAT function usage with CHAR(13)

Now, we will replace CHAR(13) expression with CHAR(10) expression and re-execute the same query:

CONCAT function usage with CHAR(10)

At the same time, we can use CHAR(13) and CHAR(10) together. This usage type could be a good option when we want to generate a line break. Now, we will make a demonstration of it:

CONCAT function usage with CHAR(13) and CHAR(10)

Conclusion

In this article, we have learned the CONCAT function in SQL using various examples. CONCAT function is a very useful option to concatenate the expressions in the SQL.

Esat Erkec

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec
Esat Erkec
150 Views