Tutorial Gateway

  • C Language
  • Java
  • R
  • SQL
  • MySQL
  • Python
  • BI Tools
    • Informatica
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • QlikView
  • Js

SQL CONCAT_WS Function

by suresh

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

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

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

SQL CONCAT_WS Function 1

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

SQL CONCAT_WS Function 2

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.

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

SQL CONCAT_WS Function 3

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

Placed Under: SQL

Trending Posts

SQL WHERE Clause

Python NumPy Trigonometric Functions

C Program to Check for Prime Armstrong or Perfect Number

MySQL GET_FORMAT Function

C Program to Remove Last Occurrence of a Character in a String

SQL ERROR MESSAGE

Tableau Bump Chart

Python Program to find Area Of a Triangle

C Program to Check Triangle is Equilateral Isosceles or Scalene

How to UPDATE from SELECT in SQL Server

  • C Programs
  • Java Programs
  • SQL FAQ’s
  • Python Programs
  • SSIS
  • Tableau
  • JavaScript

Copyright © 2019 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy