MS SQL Server CONCAT_WS() Function

Sometimes you may need to concatenate or join multiple strings with decimetre  in MS SQL Server. There is a function called CONCAT_WS in MS SQL Server by which you can do this. CONCAT_WS function returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner. It separates those concatenated string values with the delimiter specified in the first function argument.

Let’s see the function syntax:

CONCAT_WS (separator,string_value1,string_value2[,string_valueN]... );

In this syntax:

• Parameter separator is an expression of any character type (char, nchar, nvarchar, or varchar).
• The CONCAT_WS function takes a variable number of string_value and concatenates (or joins) them into a single string. It separates those concatenated string values with the delimiter specified in the first function argument. CONCAT_WS requires a separator argument and a minimum of two other string value arguments; otherwise, CONCAT_WS will raise an error. CONCAT_WS implicitly converts all arguments to string types before concatenation.
• Parameter string_value1, string_value2, string_valueN  are expression of any type.
• The implicit conversion to strings follows the existing rules for data type conversions. See CONCAT for more information about behavior and data type conversions.
• A string value whose length and type depend on the input. Finally function returns concatenated string.

The following example uses CONCAT_WS function to join the strings.

DECLARE @v1 varchar(30) = 'MS';
DECLARE @v2 varchar(30) = 'SQL Server';
DECLARE @v3 varchar(30) = 'is a';
DECLARE @v4 varchar(30) = 'RDBMS';
SELECT CONCAT_WS(' ',@v1,@v2,@v3,@v4) result;

Here is the result set.

I have an another post MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server.

In this tutorial, I have shown you how to join multiple string with delimiter using CONCAT_WS function in MS SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.