Home » SQL Server CONCAT_WS() Function

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 conactenates three strings into single string, by seprating 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 prameter 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

 




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.