New T-SQL string functions in Azure SQL Database

1 марта 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.

Бесплатная учетная запись

Получите $200 на счет в Azure и возможность использовать популярные службы совершенно бесплатно на протяжении 12 мес.

Начните использовать бесплатно

Visual Studio

Подписчики Azure экономят до $1800 в год

Активировать сейчас

Стартапы

Присоединитесь к программе BizSpark и получите бесплатные службы Azure

Дополнительные сведения