New T-SQL string functions in Azure SQL Database

New T-SQL aggregate and string functions are available in Azure SQL database:

• STRING_AGG () aggregate that concatenates values from a table by using the specified separator. An example of a query that returns all departments with a list of employee phone numbers separated with commas, and a list of email addresses separated with semicolons, is:

SELECT DepartmentName, STRING_AGG ( Phone, ',' ) as Phones, STRING_AGG ( EmailAddress, ';' ) WITHIN GROUP (ORDER BY EmailAddress) as Emails
FROM Employee
GROUP BY DepartmentName

Note that you can use an additional WITHIN GROUP clause to sort the values before concatenation. In this example, email addresses are sorted alphabetically.

• TRIM() function that can remove any set of characters from both sides of the string. The following example returns descriptions of the articles where spaces are trimmed in first column; and dots, commas, and semicolons are trimmed in the second column: 

SELECT TRIM (Description), TRIM( '.,!' FROM Description)
FROM Article

• CONCAT_WS() function that concatenates values by using the separator defined as the first parameter. The following query returns the name of the customer and the address, with address parts separated with commas:

SELECT Name, CONCAT_WS ( ',', AddressLine1, AddressLine2, Town, Region, Country, ZipCode) as FullAddress
FROM Customers

All new functions are also available in natively compiled stored procedures, functions, and triggers.