SQL Server CONCAT_WS() Function Use With Example

Summary: In this tutorial, you will learn SQL Server CONCAT_WS() Function and how to use them to join multiple strings together with a separator.

Definition and Usage

SQL CONCAT_WS() means concatenate with separator. The SQL Server CONCAT_WS() function concatenates two or more strings into one string with a separator.
The following is given below syntax of the CONCAT_WS() function:

CONCAT_WS syntax

CONCAT_WS(separator,input_string1,input_string2,[…input_stringN]);

In this syntax:

  • the separator is a character-based expression that evaluates to any character of the type CHAR, VARCHAR, NVARCHAR, or NCHAR.
  • input_string1 to input_stringN are the expressions of any type. Therefore, the CONCAT_WS() function implicitly converts the values of non-character type to character type before the concatenation.
  • The CONCAT_WS() function used to joins the input strings into a single string. furthermore, it separates those concatenated strings with the separator specified in the first argument.
  • The CONCAT_WS() function treats NULL as an empty string of type VARCHAR(1). Therefore, it also does not add the separator between NULLs. The CONCAT_WS() function can cleanly join strings that may have blank values.
.
Note: The CONCAT_WS() requires at least two input strings, therefore, means that if pass zero or one input string argument, then the function will raise an error.

SQL Server CONCAT_WS() function Examples

Let’s see some examples to understand, how to use them.
A) Using CONCAT_WS Function to join literal strings with a separator
The following example uses the CONCAT_WS function to join two literal strings into one. Therefore, it separates two concatenated value using a space:

SELECT 
    CONCAT_WS(' ', 'David', 'Mark') full_name

Therefore, let’s execute the query to return the output.

full_name
---------
David Mark
(1 row affected)
B) Using CONCAT_WS() Function with table columns

Here, we will use the Sample Demo table "Customers".

CustomerName ContactName Address City PostalCode Country
Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 5021 Mexico
Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 5023 Mexico
Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France
Bs Beverages Victoria Ashworth Fauntleroy Circus London 87000 UK
Bottom-Dollar Markets Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen 45300 Canada
Bon app Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid 28023 Spain
Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London 98760 UK
Comércio Mineiro Pedro Afonso Av. dos Lusíadas, 23 São Paulo 5432 Brazil
Chop-suey Chinese Yang Wang Hauptstr. 29 Bern 3012 Switzerland
Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 México D.F. 5022 Mexico
Du monde entier Janine Labrune 67, rue des Cinquante Otages Nantes 44000 France
Drachenblut Delikatessend Sven Ottlieb Walserweg 21 Aachen 52066 Germany
Ernst Handel Roland Mendel Kirchgasse 6 Graz 8010 Austria
Eastern Connection Ann Devon 35 King George London 11036 UK
Franchi S.p.A. Paolo Accorti Via Monte Bianco 34 Torino 10100 Italy
Furia Bacalhau e Frutos do Mar Lino Rodriguez Jardim das rosas n. 32 Lisboa 1675 Portugal
France restauration Carine Schmitt 54, rue Royale Nantes 44000 France
Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
Folk och fä HB Maria Larsson Åkergatan 24 Bräcke 885634 Sweden
Folies gourmandes Martine Rancé 184, chaussée de Tournai Lille 59000 France
FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain

The following statement uses the CONCAT_WS() function to join values in the CustomerName and City columns of the Customers table as given above table. Therefore, it separates City and CustomerName by a comma (,) and space:

SELECT 
    CustomerName, 
    City,  
    CONCAT_WS(', ', City, CustomerName) CustomerDetails
FROM 
    Customers
ORDER BY 
    CustomerName;

The following given below picture shows the output:

CONCAT_WS Function

C) Using CONCAT_WS() Function with NULL

The given below the following statement demonstrates how the CONCAT_WS() function handles input strings that have NULL values:

SELECT 
    CONCAT_WS(',', 11, 12, NULL, NULL, 13);

result
----------------------------------------
11,12,13
(1 row affected)

As you can see that clearly from the output, the function ignores the NULL and don’t add the separator between NULL values.

Conclusion

In this tutorial, you have learned SQL CONCAT_WS Function and How to use them to concatenate strings with a separator. I hope you will enjoy it!

About Author

admin