Microsoft looks very serious this time to move people from other databases to SQL Server. As with SQL Server 2016 & 2017 you can see lot of Built-in function added, which were present in other databases from long back, will ease database development in SQL Server.

One of this function is TRANSLATE() function, which can be used like a REPLACE() function, and would avoid using REPLACE() function multiple times in a query.

Syntax:

TRANSLATE ( inputString, characters, translations)

Note: characters and translations params should have same length.

–> Consider this example I’ve taken from MSDN:

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');
GO

Output:

Input

Output

2*[3+4]/{7-2}

2*(3+4)/(7-2)

–> If you had to do same with REPLACE() function then you would end up writing multiple & nested REPLACE() function, like:

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 2017”) 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:

–> 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