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:
CONCAT_WS() with NULL
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:
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:
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:
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:
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:
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:
StudentInfo --------------------------------------- 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: