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.

Syntax

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:

SELECT column_names, CONCAT_WS(column1, column2) 
FROM table_name
ORDER BY column_name;

Example

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:

SELECT id, admission_no, first_name, last_name,
    CONCAT_WS(', ', first_name, last_name) AS full_name
FROM Student
ORDER BY 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:

SELECT 
STRING_AGG(CONCAT_WS(',', first_name, last_name, age, city), char(13)) AS StudentInfo
FROM Student;

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:

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.

Next TopicSQL Server Replication

← prev next →