SQL Server CONCAT_WS Function

SQL Server CONCAT_WS function is a string function that is used to concatenates two or more strings together with a separator.

It separates those concatenated string values with the delimiter specified in the first function parameter.

• CONCAT_WS Function requires a separator parameter and a minimum of two other string value parameters. Otherwise, CONCAT_WS will gives an error.
• It implicitly converts all arguments to string types before concatenation.
• It ignores null values during concatenation and does not add the separator between null values.
• If it receives parameters with all NULL values then it returns an empty string.

SYNTAX

CONCAT_WS ( separator, parameter1, parameter2 [, parameterN]... )

separator
An expression of any character type (char, nchar, nvarchar, or varchar).

parameter1, parameter2,.. parameterN
An expression of any type.

Lets Look at an example of CONCAT_WS() Function in SQL Server.

Using CONACT_WS function with multiple strings

Following statement uses CONACT_WS() Function to concatenates three strings into single string, by separating them with SPACE.

SELECT 
 CONCAT_WS(' ', 'Microsoft', 'SQL' ,'Server') as string

 

Using CONACTE_WS function with NULL values

CONACTE_WS Function ignores the null values during the concatenation, as you can null values from parameter 1 and parameter 3 are ignored during concatenation.

SELECT
CONCAT_WS('_',null, 'Microsoft', null, 'SQL' ,'Server') as string

 

CONCAT_WS Function with all parameter values are null

If all the parameters with values null are passed to CONCAT_WS Function, It returns empty string of type varchar(1).

SELECT
 CONCAT_WS('_',null, null, null) as string

 

Using CONCATE_WS() function with SQL Server table

SELECT FirstName, MiddleName, LastName,
CONCAT_WS(' ',FirstName, MiddleName, LastName) as FullName 
from DimCustomer

 

Also Read..

SPACE()

TRANSLATE()

STUFF()

SQL COALESCE()

STRING_SPLIT()