New T-SQL string functions in Azure SQL Database

지금 사용 가능 현재 일반 공급됩니다.

업데이트된 날짜: 2017년 5월 15일 월요일

New T-SQL aggregate and string functions will soon be 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'll be able to 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 will also be available in natively compiled stored procedures, functions, and triggers.

 

Learn more about SQL Database.