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.

SQL CONCAT_WS Syntax

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

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

For this demonstration, We are going to use the below shown data

SQL CONCAT_WS Function Example 1

The SQL String CONCAT_WS Function returns a string by concatenating two or more strings using separator. The following query will show multiple ways to use 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

Below statement concatenate str1 and str2 using space. We also assigned new name using SQL 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