Archive

Posts Tagged ‘String Concat’

New built-in function CONCAT_WS() in SQL Server 2017

January 20, 2017 1 comment

 
In my previous posts I discussed new Functions introduced in SQL Server vNext (or 2018), like STRING_AGG(), TRIM(), TRANSLATE().
 

Here in this post I’ll discuss about one more new function i.e. CONCAT_WS(), here “_WS” means “With Separator”.

This is very similar to the existing CONCAT() function introduced back in SQL Server 2012, which concatenates a variable number of arguments or string values.

The difference is the new function CONCAT_WS() accepts a delimiter specified as the 1st argument, and thus there is no need to repeat the delimiter after very String value like in CONCAT() function.

Also the new CONCAT_WS() function takes care of NULL values and do not repeat the delimiter, which you can see in 2nd example below.
 

Syntax:

CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )

 

–> Example #1:

With CONCAT_WS() we will use the delimiter just once and it concatenates the names separated by ‘-‘, and do not repeat the hyphen where the middle name is NULL.

USE [AdventureWorks2014]
GO

SELECT TOP 10 
	CONCAT_WS(' - ', FirstName, MiddleName, LastName) as FullName, 
	FirstName, MiddleName, LastName
FROM [Person].[Person]

concat_ws-01
 

–> Example #2:

With CONCAT() the delimiter needs to be used after every argument, it concatenates the names separated by ‘-‘, do repeats the hyphen where the middle name is NULL.

SELECT TOP 10 
	CONCAT(FirstName, ' - ', MiddleName, ' - ', LastName) as FullName, 
	FirstName, MiddleName, LastName
FROM [Person].[Person]

concat_ws-02

Advertisement
Categories: SQL Built-in Functions, SQL Server 2017 Tags: , , , ,

String concatenation resulting NULL values – MSDN TSQL forum

April 9, 2015 Leave a comment

–> Question:

The Payments field in the below update statement always return null even though the update statement executed successfully.

What is wrong here?

DECLARE @Temp nvarchar(200), @sql nvarchar(max),@Details_InvNo nvarchar(4000), @Details_Amt nvarchar(100),@ChkNo nvarchar(100)
set @Details_InvNo = 'IZM5728'
set @ChkNo = '1-33333333'
set @Details_Amt = '100'
SET @Temp = ',[' + convert(nvarchar(max),@ChkNo) + '=' + cast(@Details_Amt as nvarchar(50)) + ']' 

UPDATE INVOICE 
SET Payments = convert(nvarchar(max),Payments) + cast(@Temp as nvarchar(max)), 
AmtDue = 100, 
AmtPaid=0 
WHERE InvNo = @Details_InvNo
GO

 

–>My Response:

As the Payment columns would be NULL and you are concatenating it with another String, the result will be NULL only. So, just put an IsNULL() over Payments column after “=” operator, like this:

DECLARE @Temp nvarchar(200), 
	@sql nvarchar(max), 
	@Details_InvNo nvarchar(4000), 
	@Details_Amt nvarchar(100),
	@ChkNo nvarchar(100)

set @Details_InvNo = 'IZM5728'
set @ChkNo = '1-33333333'
set @Details_Amt = '100'
SET @Temp = ',[' + convert(nvarchar(max),@ChkNo) + '=' + cast(@Details_Amt as nvarchar(50)) + ']' 

UPDATE INVOICE 
SET Payments = ISNULL(convert(nvarchar(max),Payments),'') + cast(@Temp as nvarchar(max)), 
	AmtDue = 100, 
	AmtPaid=0   
WHERE InvNo = @Details_InvNo
go

Or use CONCAT() function like this:

SET Payments = CONCAT(convert(nvarchar(max),Payments), cast(@Temp as nvarchar(max))),

 

Ref Link.

Categories: SQL Server Questions Tags: ,