SQL CONCAT_WS Function

The SQL Server CONCAT_WS String Function is used to combine two or more strings using the user-specified separator and returns a string. The basic syntax of the CONCAT_WS function is shown below.

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

For this concat with separator demonstration, we use the below-shown data.

Employee Table 1

SQL CONCAT_WS Function Example

The 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 function.

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

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'
CONCAT_WS Example 1

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

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

CONCAT_WS Function on Table Columns

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

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

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,CONCAT_WS(
		 ' * ', [FirstName], [LastName],'is earning', [YearlyIncome]
	        ) AS [Description]
 FROM [Employee]
CONCAT_WS Function 3
Categories SQL