New T-SQL string functions in Azure SQL Database

1. mars 2017

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.

Gratis konto

Registrer deg nå, og få $200 i Azure-kreditt

Start gratis

Visual Studio

Abonnenter får opptil $1800 per år med Azure-tjenester

Aktiver nå

Nystartede bedrifter

Bli med i BizSpark-programmet, og få gratis Azure-tjenester

Les mer