SQL CONCAT_WS Function

The SQL 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 SQL Server 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' 

SQL 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’.

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

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

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.