The SQL CONCAT_WS, one of the String Function, used to combine two or more strings using the user-specified separator and returns string.
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
TIP: Please refer SQL Concat function to understand the normal concatenation.
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 in SQL Server.
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