Javatpoint Logo
Javatpoint Logo

SQL Server CONCAT_WS Function

The CONCAT_WS is a string function that returns a single string by concatenating or joining two or more strings. It also uses delimiter as its first argument to separate those concatenated string values. The CONCAT_WS means concatenate with separator. It can accept a number, character, or date as input arguments. We ensure that this function requires at least three input arguments (including separator) and can be extended up to 254 input arguments.

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


The following are the basic syntax to illustrate the CONCAT_WS function in SQL Server:

In the above syntax, we use the following parameters:

Separator: It is an expression that can be evaluated to any character type char, nchar, nvarchar, or varchar.

input_string1, input_string2,…,input_stringN: These are the expressions of any type, such as number, character, or date.

The following are the syntax to use with the table:


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

In the statement, we have provided two input parameters and an empty string as the separator because it is in the first position. Executing the function will return the below output where we see that the function combines the result and then separate them using space:

SQL Server CONCAT_WS Function


The below example will demonstrate the CONCAT_WS() function to handle the input strings that have NULL values:

Executing the statement will return the result correctly:

SQL Server CONCAT_WS Function

We already learned that the CONCAT_WS function always requires at least two parameters and a separator as its first argument to display the result. Otherwise, SQL Server through an error. The below example explains it more clearly:

Here is the error message:

SQL Server CONCAT_WS Function

The following example will concatenate the numeric string and separate them using the comma separator:

When we execute the above statement, we will get the numeric result separated with a comma:

SQL Server CONCAT_WS Function

CONCAT_WS with Table

We can also use this 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:

SQL Server CONCAT_WS Function

Suppose we want to merge the first and last names from the student table and separate them with one comma and space. We can do this by using the below statement where the concatenated string becomes the full name:

Executing the statement will return the following result:

SQL Server CONCAT_WS Function

CONCAT_WS() to generate CSV file

A CSV is a plain text file containing a list of data separated by a comma. We can also use this function to generate the CSV file by using a comma (,) as the separator and concatenates values in admission_no, first_name, last_name, age, and city column as shown in the below statement:

Executing the statement will return the below output:

Luisa, Evans, 13, Texas 
Paul, Ward, 15, Alaska 
Peter, Bennett, 14, California 
Carlos, Patterson, 17, New York 
Rose, Huges, 16, Florida 
Mariellia, Simmons, 15, Arizona 
Antonio, Butler, 14, New York 
Diego, Cox, 13, California

CONCAT() vs. CONCAT_WS() Functions

We use the CONCAT and CONCAT_WS functions for merging or joining two or more strings. However, these functions have some differences that are pointed below:

  • The CONCAT WS() function will concatenate strings with a separator, whereas the CONCAT() method has no concept of using a separator.
  • The CONCAT() method returns NULL when any of the arguments is NULL, whereas the CONCAT WS() function returns NULL when the separator is NULL.

Youtube For Videos Join Our Youtube Channel: Join Now


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA