SQL CONCAT_WS Function

The SQL CONCAT_WS, one of the String Function, used to combine two or more strings using the user-specified separator and returns string.

TIP: Please refer SQL Concat function to understand the normal concatenation.

The basic syntax of the SQL Server CONCAT_WS function is as shown below:

SELECT CONCAT_WS ('Seperator', String 1,String 2,..,String N)
FROM [Source]

For this string concat with separator (CONCAT_WS) demonstration, we are going to use the below-shown data

SQL CONCAT_WS Function Example 1

The String CONCAT_WS Function returns a string by concatenating two or more strings using a separator.

The following query will show multiple ways to use this string Concat_ws function.

-- SQL Server String Concat_WS Function
DECLARE @Str1 varchar(50), 
        @Str2 varchar(50)
SET @Str1 = 'Learn' 
SET @Str2 = 'SQL Server'

SELECT CONCAT_WS(' ', @Str1, @Str2) AS 'ConcatText' 

SELECT CONCAT_WS(',', @Str1, @Str2) AS 'ConcatText' 

--Concating four String using comma
SELECT CONCAT_WS(' , ', 'Learn', 'SQL Server','at', 'Tutorial Gateway') AS 'ConcatText'

SELECT CONCAT_WS(' HI ', 'Learn', 'SQL Server','at', 'Tutorial Gateway') AS 'ConcatText'

OUTPUT

ANALYSIS

Within this string concat with separator example, the below statement concatenate str1 and str2 using space. We also assigned a new name using the ALIAS Column.

SELECT CONCAT_WS(' ', @Str1, @Str2) AS 'ConcatText' 

SQL CONCAT_WS Function Example 2

In this example, we are going to combine the First name, Last Name, yearly Income columns present in the Employee table, and text = ‘is earning’.

-- SQL Server CONCAT_WS FUNCTION Example
USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,CONCAT_WS(
		 ' * ', [FirstName], [LastName],'is earning', [YearlyIncome]
	        ) AS [Description]
 FROM [Employee]

OUTPUT

TIP: We used this ‘ * ‘ to insert * in-between each column. If you want a comma or something, replace * with comma