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.
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]
–> 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]
If you are thinking the new TRIM() function in SQL Server vNext is just a combination of LTRIM() & RTRIM() functions, then you are wrong :). It’s more than that and we will check it today !
– LTRIM() function is used to truncate all leading blanks, or white-spaces from the left side of the string.
– RTRIM() function is used to truncate all trailing blanks, or white-spaces from the right side of the string.
–> Now, with teh new TRIM() function you can do both, but more than that.
Usage #1: TRIM() function will truncate all leading & trailing blanks from a String:
SELECT TRIM (' Manoj Pandey ') as col1, LTRIM(RTRIM(' Manoj Pandey ')) as col2
Usage #2: Plus it can be used to remove specific characters from both sides of a String, like below:
SELECT TRIM ( 'm,y' FROM 'Manoj Pandey') as col1, TRIM ( 'ma,ey' FROM 'Manoj Pandey') as col2, TRIM ( 'm,a,e,y' FROM 'Manoj Pandey') as col3
Thus with the above query you can see that you can trim characters too, by providing leading & trailing characters, but should be in same sequence as your string is.
Also for Col2 & Col3 we have provided Trimming Characters in 2 different ways, but got the same output.
–> Note: I just mentioned above that the leading & trailing characters should be in same sequence. If you provide in different sequence like below you won’t get desired results.
SELECT 'Manoj Pandey' as st, TRIM ( 'a,n' FROM 'Manoj Pandey') as Col1, TRIM ( 'm,e' FROM 'Manoj Pandey') as Col2, TRIM ( 'm,o,y,e' FROM 'Manoj Pandey') as Col3
Like for Col3 you cannot get rid of middle characters (like ‘o’ and ‘n’) until and unless they become leading or trailing characters.
In one of my [previous post] I discussed about a new function STRING_SPLIT() introduced in SQL Server 2016, which splits a Sentence or CSV String to multiple values or rows.
Now with the latest CTP 1.x version of SQL Server vNext (I’m calling it “SQL Server 2018”) a new function is introduced to just do its reverse, i.e. Concatenate string values or expressions separated by any character.
The STRING_AGG() aggregate function takes all expressions from rows and concatenates them into a single string in a single row.
It implicitly converts all expressions to String type and then concatenates with the separator defined.
–> In the example below I’ll populate a table with States and Cities in separate rows, and then try to Concatenate Cities belonging to same States:
USE tempdb GO CREATE TABLE #tempCityState ( [State] VARCHAR(5), [Cities] VARCHAR(50) ) GO INSERT INTO #tempCityState SELECT 'CA', 'Hanford' UNION ALL SELECT 'CA', 'Fremont' UNION ALL SELECT 'CA', 'Los Anggeles' UNION ALL SELECT 'CO', 'Denver' UNION ALL SELECT 'CO', 'Aspen' UNION ALL SELECT 'CO', 'Vail' UNION ALL SELECT 'CO', 'Teluride' UNION ALL SELECT 'WA', 'Seattle' UNION ALL SELECT 'WA', 'Redmond' UNION ALL SELECT 'WA', 'Bellvue' GO SELECT * FROM #tempCityState
State Cities CA Hanford CA Fremont CA Los Anggeles CO Denver CO Aspen CO Vail CO Teluride WA Seattle WA Redmond WA Bellvue
–> To comma separate values under a single row you just need to apply the STRING_AGG() function:
SELECT STRING_AGG(Cities, ', ') as AllCities FROM #tempCityState -- Use "WITHIN GROUP (ORDER BY ...)" clause to concatenate them in an Order: SELECT STRING_AGG(Cities, ', ') WITHIN GROUP (ORDER BY Cities) as AllCitiesSorted FROM #tempCityState
–> Now to Concatenate them by States, you just need to group them by the State, like any other aggregate function:
SELECT State, STRING_AGG(Cities, ', ') as CitiesByStates FROM #tempCityState GROUP BY State -- Use "WITHIN GROUP (ORDER BY ...)" clause to concatenate them in an Order: State, STRING_AGG(Cities, ', ') WITHIN GROUP (ORDER BY Cities) as CitiesByStatesSorted FROM #tempCityState GROUP BY State
You can check more about STRING_AGG() on MSDN.