Concatenation in Transact-SQL

Concatenation in Transact-SQL
5 (100%) 1 vote

Introduction

Assuming you maintain a table containing customer data, and your boss asks you to send him the current list of customers and their phone numbers. You would typically extract the data and send him a spreadsheet with rows and columns. You could also decide to be a bit stylish and send him the required information in a more people-friendly format. SQL Server provides functions that allow us to achieve this by combining English expressions with data within tables to present an output that is easier for non-technical people to read. These functions can also be put to more subtle uses.

The CONCAT Function

The CONCAT function accepts two or more string arguments and returns the combination of such strings as a single expression. This can be useful if you want to display the contents of different columns as a single expression. A simple example of using this function is shown in Listing 1.

Notice the variations of this statement using the space and the results in Fig. 1.

Statement variation

Fig. 1. Simple CONCAT Statement

If we try to use the CONCAT statement with an input value of INT data type, SQL Server does an implicit conversion and still returns a string output as shown in Fig 2. We can confirm that this is what is actually happening by digging deep into the details of the statement in Listing 2. First of all, take a look at the structure of the table we are interested in. Fig 2 shows us that the PhoneNumber# and FirstTranDate columns are BIGINT and DATETIME columns respectively.

Structure of customer table

Fig. 2. Structure of the Customer Table

Taking a quick look at the execution plan shows us that SQL Server performs an implicit conversion on the PhoneNumber1 column. This will be the same if the column was the date data type as show in Listing 4 and Fig. 4. The CONCAT function performs implicit conversion based on the rules outlined in the chart shown in Fig. 6.

Implicit conversion

Fig. 3. Implicit Conversion of BIGINT data type to VARCHAR

Implicit Conversion of DATETIME

Fig. 4. Implicit Conversion of DATETIME data type to VARCHAR

Implicit Conversion of BIGINT

Fig. 5. Implicit Conversion of BIGINT data type to VARCHAR

Data Type Conversion

Fig. 6. Data Type Conversion in SQL Server

The primary use case for this function can be deduced from the demonstrations above. An example would be a case where some information needs to be displayed on a dashboard or web page in more friendly language using data from a number of columns or even separate tables.

The CONCAT_WS Function

The CONCAT_WS function is an extension of the CONCAT function. It allows us to specify a desired separator as the first parameter. Listing 4 shows us a modification of one of the statements we previously used in Listing 1.

Notice that CONCAT_WS makes it simpler to construct a statement with space as a separator as compared to introducing a space as an argument after each argument.

Concatenation with the “+” Sign

SQL Server supports the use of the “+” sign to achieve what the CONCAT function does in a much simpler manner. This approach is typically used to generate T-SQL statements when you need to perform operations on a large number of objects. Listing 7 shows how we can generate a Statistics update batch for all tables in the Exam database.

Notice the square brackets in the second statement. It is useful when dealing with a system object with spaces or special characters.

Once the output is generated, it can be used to create users in any desired database as shown in Listing 7. Note that we have added a filter for the login names we are interested in. This approach can be used to generate all sorts of statements and call such statements within the same session. A more complex example is the following statements which creatively rebuild all indexes in any database. (See Listings 8 and 9).

The query in Listing 10 shows how we can combine strings with dates explicitly converted to strings. The String Concatenation is used to generate a common backup path variable which is later used within the SP_MSFOREACHDB function.

Conclusion

In this article, we have shown a few ways to use concatenation in SQL Server. We have given examples of the CONCAT function, the CONCAT_WS function and the use of the “+” sign. All three methods can be very useful in generating statements by combining values from different columns or simply to display information in a desired people-friendly format. Microsoft documentation has more information about the syntax and capabilities of these functions.

References

Cast and Convert (Transact-SQL)

Concat Transact-SQL

Concat_ws Transact-SQL

String Concatenation

Kenneth Igiri

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud.
Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.
Kenneth Igiri

Latest posts by Kenneth Igiri (see all)