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..
2,159 total views, 1 views today