SQL CONCAT_WS Function

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

In this article we will show you, How to write string concat_ws in SQL Server with example.

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

CONCAT_WS Syntax

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 demonstration string concat with seperator, 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 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 new name using ALIAS Column.

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

SQL CONCAT_WS Function Example 2

In this example,We are going 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 comma or something, replace * with comma