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
, orvarchar
). - 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.
result |
---|
MS SQL Server is a RDBMS |
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.