SQL Server CONCAT Function

The SQL Server CONCAT is a string function used for joining two or more strings. If it finds NULL values, it converts them into an empty string when the result is displayed. This method can accept multiple arguments but return only the single string as an output. It can accept a number, character, or date as input arguments. We ensure that this function requires at least two input arguments and can be extended up to 254 input arguments.

NOTE: The CONCAT function implicitly converts the non-character string values into a string before concatenation.

Syntax

The following are the basic syntax that illustrates the CONCAT function in SQL Server:

The following are the syntax to use with the table:

Example

Let us understand the working of CONCAT function with the basic example given below:

In the statement, we have provided five input parameters, including an empty string. Executing the function will return the below output where we see that the function combines the result into a single string:

It is another example where we use the NULL value with the CONCAT function:

Executing the statement will return the result correctly:

As we learned above, the CONCAT function must require at least two arguments. Otherwise, give an error. The below example explains it more clearly:

Here is the error message:

The following example will concatenate the numeric string into a single string:

OR,

When we execute the above statements, we will get the same result as shown below:

CONCAT Function with Table

We can also use the CONCAT function with the columns of the table. Here we are going to take a student table for a demonstration of this function. Suppose the student table contains the following data:

Suppose we want to merge the first name and last name from the student table. We can do this by using the below statement, and the concatenated string becomes the full name:

Executing the function will display the following output: